SQL Server database contains different commands for different operations but half knowledge about these commands can make a huge trouble for a DBA. So always take the complete knowledge of commands.
Well, I was reading a question about SQL Server database commands: DBCC CHECKDB, WITH CHECKSUM and VERIFY ONLY and then I join these three strong commands together to make a new article for the readers. Some users believe that, they can use WITH CHECKSUM option to take the backup of the SQL Server database while this is again half knowledge. Now, we will see the use of these three commands.
DBCC CHECKDB: It checks the physical and logical integrity of the database objects.
Why we use DBCC CHECKDB: There are two types of tables in the SQL Server database:
User can perform the DBCC CHECKDB operation on the database which contains memory optimized tables but it works on disk based tables. Since DBCC option is not available for memory optimized tables then user should take the backup of the database regularly (depends on the work plan) to prevent database from memory optimized tables.
If your database is corrupted and you are trying to take a backup of it then, you will also get a corrupted backup of your SQL Server database. In this case, you can check the database by WITH CHECKSUM option. When a user successfully create a backup of the database that means there is no corruption in your database. This is one more option to check the corruption.
WITH CHECKSUM: First of all, the permission to perform read and write on the media (If you are not the member of sysadmin) to a user is must otherwise you will get the permission issue.
WITH CHECKSUM option is used to test page checksums that exists on the data file pages and these pages backed up during the backup process. If a bad page checksum is found then the backup process will stop automatically. In an emergency, a user can override this by using WITH CONTINUE_AFTER_ERROR. Completion of the backup process is also indicating that, there are no broken page checksums.
WITH CHECKSUM option during Backup Process: To enable the WITH CHECKSUM option during backup process use the following commends:
BACKUP DATABASE AdventureWorks2012
TO DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO
Note: User can also enable this option by using trace flag 3023. If the trace flag 3023 is turned on that means the CHECKSUM option is automatically enable for the backup command. It is also possible by the SSMS. Go to Options page->Reliability-> Perform checksum before writing to media.
If a user wants to disable it then, use WITH NO_CHECKSUM option.
WITH CHECKSUM option during restore process:
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO
To disable it, Use WITH NO_CHECKSUM option to disable it.
RESTORE VERIFYONLY: Assume a situation, when a user takes the backup of SQL Server database successfully that means there is no corruption in the database. After taking its backup, if the backup got corrupted then what will a user do? Its answer is RESTORE VERIFYONLY will be useful for user. This command will check whether a database is corrupted or not.
RESTORE VERIFYONLY FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
Note: Before restoring the database from the backup, please check the database by using this command to save your time.
Conclusion: We have seen the uses of important commands of the SQL Server database. I hope it will make your work easy and help you to make your database corruption free.
Well, I was reading a question about SQL Server database commands: DBCC CHECKDB, WITH CHECKSUM and VERIFY ONLY and then I join these three strong commands together to make a new article for the readers. Some users believe that, they can use WITH CHECKSUM option to take the backup of the SQL Server database while this is again half knowledge. Now, we will see the use of these three commands.
DBCC CHECKDB: It checks the physical and logical integrity of the database objects.
Why we use DBCC CHECKDB: There are two types of tables in the SQL Server database:
- Disk-based tables
- Memory optimized tables
User can perform the DBCC CHECKDB operation on the database which contains memory optimized tables but it works on disk based tables. Since DBCC option is not available for memory optimized tables then user should take the backup of the database regularly (depends on the work plan) to prevent database from memory optimized tables.
If your database is corrupted and you are trying to take a backup of it then, you will also get a corrupted backup of your SQL Server database. In this case, you can check the database by WITH CHECKSUM option. When a user successfully create a backup of the database that means there is no corruption in your database. This is one more option to check the corruption.
WITH CHECKSUM: First of all, the permission to perform read and write on the media (If you are not the member of sysadmin) to a user is must otherwise you will get the permission issue.
WITH CHECKSUM option is used to test page checksums that exists on the data file pages and these pages backed up during the backup process. If a bad page checksum is found then the backup process will stop automatically. In an emergency, a user can override this by using WITH CONTINUE_AFTER_ERROR. Completion of the backup process is also indicating that, there are no broken page checksums.
WITH CHECKSUM option during Backup Process: To enable the WITH CHECKSUM option during backup process use the following commends:
BACKUP DATABASE AdventureWorks2012
TO DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO
Note: User can also enable this option by using trace flag 3023. If the trace flag 3023 is turned on that means the CHECKSUM option is automatically enable for the backup command. It is also possible by the SSMS. Go to Options page->Reliability-> Perform checksum before writing to media.
If a user wants to disable it then, use WITH NO_CHECKSUM option.
WITH CHECKSUM option during restore process:
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO
To disable it, Use WITH NO_CHECKSUM option to disable it.
RESTORE VERIFYONLY: Assume a situation, when a user takes the backup of SQL Server database successfully that means there is no corruption in the database. After taking its backup, if the backup got corrupted then what will a user do? Its answer is RESTORE VERIFYONLY will be useful for user. This command will check whether a database is corrupted or not.
RESTORE VERIFYONLY FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
Note: Before restoring the database from the backup, please check the database by using this command to save your time.
Conclusion: We have seen the uses of important commands of the SQL Server database. I hope it will make your work easy and help you to make your database corruption free.
No comments:
Post a Comment