Perform a single table restore from the
most recent backup. You must be careful of and account for any key relationships in the
database. If a single table restore does not work due to data conflicts or the table
contains a text or image column, continue on to the final suggestion.
- If replication is installed, stop replication
- Dump the transaction log using the no_truncate option
- Restore the most recent backup
- Apply all transaction logs
- 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 backup of the database
The final way to recover is to restore the entire database and apply all transaction
logs. This is also the safest way in order to ensure you account for any primary - foreign
key relationships.
- If replication is installed, stop replication
- Dump the transaction log using the no_truncate option
- Restore the most recent backup
- Apply all transaction logs
- 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
Corrupt System Table
There is exactly one way to recover a corrupt system table. That is to restore from the
most recent backup.
After restoring the master database, there are two ways to restore the rest of the
system.
- If replication is installed, stop replication on all databases
- Place all databases in dbo use only mode
- Run setup and run the rebuild master option
- Restore the master database from the last backup
- Stop SQL Server
- Restart in single user mode
- Using isql/w, log in to the server
- If master fails to restore cleanly
- Rebuild master
- Turn on allow updates
- BCP in the file from syslogins
- Turn off allow updates
- Do NOT attempt another restore of master and do NOT restore msdb
- Run script to recreate all dump devices
- Run script to recreate all tasks with the exception of replication tasks
- Run disk reinit and disk refit on each database
- Stop and start SQL Server
- Verify all databases recovered correctly
- 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
