If the above procedure does not work or a
database was reset to suspect after you reset the status, you have corruption that has
propagated to the user databases. You must fix this by the following procedure.
- From isql/w, drop the suspect database by using the DBCC dbrepair command
- Drop the devices the database was on. (This is another extremely good reason to have
each of your databases on separate devices.)
- Open Windows Explorer and delete the physical .dat file for the device your just dropped
in SQL Server. (No, SQL Server does not remove the file.)
- Using the device creation script you generate each night using the script in Appendix B,
recreate the devices you just dropped. (<filename>)
- 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>)
- Restore the database from the most recent full backup.
- Apply any transaction logs.
- Run a full battery of DBCCs (checkdb, newalloc, checkcatalog) on the restored database.
- If replication is installed, remove replication for that database
- If the database is replicated, verify all replication components have been eliminated
- If the database is replicated, reinstall replication
- If the database is replicated, verify and test installation of all components
- Perform a full server backup.
Bad master backup
If the backup of master is bad, you still have some options. The procedure to recover
the server is as follows:
- Rebuild master
- Make sure the server is in single user mode.
- Start isql/w and issue a disk reinit for each of the database devices you should have on
the server. (You can get the needed information from the output of the disk init script
included in Appendix B that you should be running each night.) (<filename>)
- Execute a disk refit.
- Stop and restart SQL Server.
- Your server should come back to life with all of the databases intact.
- Recreate each of the backup devices.
- Execute the script generated by the proc in Appendix D to recreate any tasks.
- Turn on allow updates and stop and restart SQL Server
- BCP in the output file from syslogins.
- Turn off allow updates
- Stop and start SQL Server
- If replication is installed, remove replication for that database
- If the database is replicated, verify all replication components have been eliminated
- If the database is replicated, reinstall replication
- 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
