1. If replication is installed, stop replication on all databases
2. Dump the transaction logs using the no_truncate option
3. Locate the most recent backup
4. Run setup and run the rebuild master option
5. Restore the master database from the last backup
6. Restore the msdb database from the last backup
7. Restore all user databases from the last backup
8. Restore all required transaction logs
9. If replication is installed, remove replication for that database
10. If the database is replicated, verify all replication components have been eliminated
11. If the database is replicated, reinstall replication
12. If the database is replicated, verify and test installation of all components

Corrupt User Database

If a user database gets corrupted, you must apply the most recent backup. As an additional safety precaution you should drop the database using either the drop database command or the DBCC dbrepair command. You then recreate it from the script you should have that is generated by sp_help_revdatabase. This ensures that you are not carrying around anything from the corrupted database.

1. If replication is installed, stop replication
2. Execute a dump transaction with the no_truncate option
3. Drop the database using either a drop or DBCC dbrepair
4. Recreate the database using the file called <filename>
5. Restore the database from the most recent backup
6. Apply any transaction logs
7. If replication is installed, remove replication for that database
8. If the database is replicated, verify all replication components have been eliminated
9. If the database is replicated, reinstall replication
10. If the database is replicated, verify and test installation of all components

Suspect database

A suspect database can occur for many reasons. In some cases SQL Server simply could not open the .dat file that contains the database or it simply gets lost and marks it suspect. In a few cases, the database actually corrupts to such a level that SQL Server can no longer access it. You must fix this by the following procedure.

1. First you must investigate the NT event log and SQL Server error log to see if anything was reported that led to the database being marked suspect.
2. If no errors can be found, execute an sp_resetstatus
3. Stop and start SQL Server

6.5 Disaster Recovery Plan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18