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.
- Cure the error message & restart the restore operation.
- Continue with the error message.
- 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.