Welcome to SqlServer-QA.net Sign in | Join | Help

SQL Server Storage Engine & Tools (SSQA.net)

SQL Server Tools includes storage engine that includes a complete set of graphical tools and command prompt utilities that allow users, programmers, and administrators. SSMS, SSRS, SSIS & SSAS are most commonly used tools.
Restore corrupted database - any clause using RESTORE statement?

Do you know you can try restore a corrupted database backup using simple clause within RESTORE statement.

You may be aware that SQL 2005 version can perform an optional computation a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page.  SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.

For that you have to mention CHECKSUM option within the BACKUP statement, this will enable the pages to be backed up as they exist and will not be modified by the backup. But keep in mind this will have slow generation of backup process in terms of performance, the throughput & workload are affected and don't forget to monitor the CPU to ensure no adverse impact in using them. Though this provides an improved error detection you can take up only after testing the process completely.

Also you could take up verification of backup using RESTORE VERIFY ONLY statement, as this will only checks for errors on the file but will not assure the backup is completely clean or the restore operation can be completed without any error. So taking advantage of the option to add "WITH CONTINUE_AFTER_ERROR", which causes the restore operation to continue in the face of minor corruptions of the backup image.

Finally if the backup file cannot be opened for any reason of bad checksum then using these options are useless and to proceed further you migth need a clean backup file again.

Posted: Sunday, August 05, 2007 10:48 AM by SQL Master

Comments

SSQA.net - SqlServer-QA.net said:

Do you know you can try restore a corrupted database backup using simple clause within RESTORE statement

# August 5, 2007 11:32 AM

Other SQL Server Blogs around the Web said:

Do you know you can try restore a corrupted database backup using simple clause within RESTORE statement

# August 6, 2007 9:53 PM

SQL Server News said:

RestoreCorruptedDatabase

# August 7, 2007 7:58 AM
Anonymous comments are disabled