How to recover corrupt SQL server backup file database?

Published by Jacob Martin on October 20, 2014

Corruption is a major problem that may arise with the SQL Management Studio Server. It becomes very easy when you have created a backup file of your SQL Server but after the corruption of your SQL Server or database you look for your backup file for restoring your original database unless you have checked the validity, how can you be sure that it can work well in disaster recovery situation?

For checking the backup file integrity you need to look for two things:

1.  Integrity of the database has being backed up
2. Integrity of the backup file itself

For checking the integrity of the database has being backed up: It is easy to run some form of DBCC consistency must be checked before the backup taking the full backup is necessary or less you may backup the corrupt database. For example: if you are dealing with a customer who has unfortunately had a PFS page header that got corrupted which was unable to be repaired using the DBCC in the database and found that it was also present in the backup. Then you would have no choice of restoring the SQL database using a weed old backup or exporting of all the data into a new data neither can be successful.

Sometimes it becomes impossible to run the CHECKDB on the source database commonly on the VLDB systems or system that has high, continuous workload where the extra overhead cannot be bared for the length of the CHECKDB. In such cases it is better to run the CHECKDB on the restored copy of database. This does not prove that the backup file valid but the source database has not corrupted at the time the backup has been taken. It is common that CHECKDB cannot be run properly on system. The only problem with the common strategy on systems is where CHECKDB comes back with errors. You cannot tell that the restored database or the source database has been corrupt and is forced to go back to the source database and run another CHECKDB.

For checking the integrity of the backup file itself, you need validate the backup. The other thing you should do before doing the backup of SQL Server 2005 by using the WITH CHECKSUM option on the backup command. This will do two things while you backup using this:

• Checks and evaluates all the existing pages checksums or torn-page for the protection of database pages.
• You can calculate the checksum over the whole backup stream and save them on the backup.

 It helps you to ensure that the database is being backed up which does not have any corruption that is caused by the IO Subsystem.

Once you take the backup it is possible to verity all of this with RESTORE … WITH VERIFY ONLY. This helps you to do everything writing bytes to disk, torn-page protection on pages in the backup or including rechecking any existing page checksums and recalculating and checking the checksum on the entire backup.

SO there are many things that you can do with the corrupt backup when the time comes to use the corrupt backup.  There are many options for restoring the backup by CONTINUE_AFTER_ERROR which helps you to restore the page checksum failure or corruptions in the backup stream. In worst cases you can even use the REPAIR_ALLOW_DATA_LOSS for the corruption related to base table data, it is much better than not able to restore the backup at all. Apart from these you can even go for the MS SQL File Repair Tool which guarantees you 100% recovery even after backup file corruption. It is a perfect solution that helps you to recover the corrupt and damaged MS SQL Server Database files accurately. It is helpful for any situation that can be caused through abnormal system shut down, virus attacks, network issues etc. It efficiently recovers the entire MDF files including triggers, tables, keys, procedures, indexes. It even helps you to rebuild a new database MDF file that makes it best tool for recover MS SQL Database.

Steps to recover corrupt SQL server backup file database

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

688 Total Views 3 Views Today