1. From isql/w, drop the suspect database by using the DBCC dbrepair command
2. Drop the devices the database was on. (This is another extremely good reason to have each of your databases on separate devices.)
3. Open Windows Explorer and delete the physical .dat file for the device you just dropped in SQL Server. (No, SQL Server does not remove the file.)
4. Using the device creation script you generate each night using the script in Appendix B, recreate the devices you just dropped. (<filename>)
5. Using the database creation script you generate each night using sp_help_revdatabase, recreate the database(s) you dropped in step 1 using a for load option. (<filename>)
6. Restore the database from the most recent full backup.
7. Apply any transaction logs.
8. Run a full battery of DBCCs (checkdb, newalloc, checkcatalog) on the restored database.
9. Perform a full server backup.
10. If replication is installed, remove replication for that database
11. If the database is replicated, verify all replication components have been eliminated
12. If the database is replicated, reinstall replication
13. If the database is replicated, verify and test installation of all components

Corrupt msdb

You will need to restore from the most recent backup. After the restore is complete, you must ensure that all of your tasks, alerts, and operators still exist and are working. You can do this by recreating each of the tasks, alerts, and operators. A script for the tasks is included in Appendix D.

1. Recreate the msdb database by utilizing instmsdb.sql from the installation CD
2. Restore from the most recent backup

Corrupt master

Having a corrupt master database is the most severe internal problem you can have in SQL Server. This will require a full rebuild of the SQL Server. The process for doing this is as follows:

1. Run setup and execute a rebuild of master.
2. When SQL Server completes the rebuild, place the server in single user mode.
3. Restore the most recent backup of master.
4. Verify that SQL Server is up and all databases are recognized.
5. If all databases are recognized and available, run a few queries of random tables in each database to verify.
6. If master fails to restore cleanly
1. Rebuild master
2. Turn on allow updates
3. BCP in the file from syslogins
4. Turn off allow updates
5. Do NOT attempt another restore of master and do NOT restore msdb
6. Run script to recreate all dump devices
7. Run script to recreate all tasks with the exception of replication tasks
7. If everything checks out at this stage, restart SQL Server
8. If replication is installed, remove replication for that database
9. If the database is replicated, verify all replication components have been eliminated
10. If the database is replicated, reinstall replication
11. If the database is replicated, verify and test installation of all components

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