Microsoft SQL Server Disaster Recovery
Appendix A: Troubleshooting and Disaster Recovery 99
Potential Restore Problems
The master database tracks all of the resources allocated to Microsoft SQL
Server. If you do not perform an offline backup after you have made a major
change in the Microsoft SQL Server configuration, restore problems can occur.
For example, for a Microsoft SQL Server configuration with five databases in
addition to the master database, you back up the master database, drop one
database (detach it from Microsoft SQL Server) and delete the files that make it
up. If you do not perform an offline backup and you restore the master database
backup at this point, it contains information for the dropped database. As a
result, Microsoft SQL Server marks the database as suspect (inaccessible by
users). You must drop the database again.
To avoid such problems, perform at least one offline backup. In addition, each
time you make a major change in the Microsoft SQL Server configuration (create
or drop a database or add a device), you should perform an offline backup.
Performing a Full Database backup of the master, model and msdb databases in
the same job generates a Microsoft SQL Server Disaster Recovery Elements
backup session. This session can be used as an off-line backup for this purpose.
Suggested Database Restore Sequence
We recommend that you restore the databases in the following order to avoid
conflicts:
1.
Restore the [master] database in single-user mode.
2.
Restore the [msdb] database in multi-user mode immediately after you
restore the master database.
3.
Restore the [model] database in multi-user mode immediately after you
restore the msdb database.
4.
Restore all other databases in normal, multi-user mode.
5.
Restore the distribution database for replication, if one exists.
Note:
These suggestions are not requirements, but following them speeds and
simplifies the disaster recovery procedure. If you restore other databases in
addition to the master database before restoring msdb, Microsoft SQL server
loses part of the backup and restore history for the other databases when msdb
is restored.
For more information, see the Microsoft SQL Server documentation.