Wednesday, March 27, 2013

How to Restore SQL server Database with Corrupt Media Backup?


Backup is a thing that can be used if necessary. In term of SQL server, Backup is a copied database that can be used to restore the database after a disaster occurs. The most popular SQL server database backups are full, differential, transaction log, file, partial, copy-only & database backup. SQL server database backups are essential to avoid the data loss from database. SQL server database administrator can restore the database from backups with the help of T-SQL command or SQL server management studio (SSMS). In respect to the SQL database, database backups are also prone to corruption and generates error message in the event of restoring the database.

Why Restore Operation Fails?
SQL server generates restore error messages when backup media is corrupt. These error messages either reported by the operating system or database checksum.

What to do now?
SQL server database administrators have three options to overcome on above problems.
  1. Cure the error message & restart the restore operation.
  2. Continue with the error message.
  3. Close the restore operation & choose any other recovery mechanism.
1. Cure the error message & restart the restore operation: Database administrator can be cured the error messages by following ways.
  • If the error occurred on the tape drive then clean or replace the tape drive.
  • If error occurred due to disk device then resolve the device error & replace the damaged file.
2. Continue with the Error Messages: DBA can specify continue_after_error option in restore statement to restore the database. It will allow the restore operation with past error message and roll forward occurs. Now DBA can apply transaction log backups to bring the database online. Below is the restore syntax with continue_after_error option.

RESTORE DATABASE database_name FROM backup_device WITH CONTINUE_AFTER_ERROR, [ NORECOVERY ]

After the specifying continue_after_error option, roll forward encounters the error message then recovery cannot be completed and database is left offline.

How to Repair Offline Database?
DBA can repair SQL database with the help of DBCC CHECKDB. DBCC CHECKDB has three repair options to repair the database, repair_rebuild, repair_fast & repair_allow_data_loss. There is no any data loss in repair_rebuild & repair_fast options but minimum amount of data may be lost in case of repair_allow_data_loss option.

Note: To learn about minimum repair level needed, first run DBCC CHECKDB with any repair option. It will suggest you the repair level and re-run the DBCC CHECKDB with suggested repair level to repair the database.

3. Close the restore operation & choose any other recovery mechanism: It is the easiest method to resolve the above problem. If you have other recovery disaster plan then it is the right time to use it.

No comments:

Post a Comment