The corruption of the master system database can be devastating for the whole system because it comprises all system tables that are necessary to work with the database system. The restore process for the master database is quite different from the same process for user-defined databases.
A damaged master database makes itself known through different failures. These failures include the following:
- Inability to start the
MSSQLSERVERprocess - An input/output error
- Execution of the
DBCCcommand points to such a failure
Two different ways exist to restore the master database. The easier way, which is available only if you can start your database system, is to restore the master database from the full database backup. If you can’t start your system, then you must go the more difficult route and use the sqlservr command.
To restore your master database, start your instance in single-user mode. Of the two ways to do it, my favorite is to use the command window and execute the sqlservr command (from the command prompt) with the option –m. Although the use of this command is more difficult, this approach allows you to restore the master database in the most cases. In the second step, you restore the master database together with all other databases using the last full database backup.
Note – If there have been any changes to the master database since the last full database backup, you will need to re-create those changes manually.
Restoring Other System Databases
The restore process for all system databases other than master is similar. Therefore, I will explain this process using the msdb database. The msdb database needs to be restored from a backup when either the master database has been rebuilt or the msdb database itself has been damaged. If the msdb database is damaged, restore it using the existing backups. If there have been any changes after the msdb database backup was created, re-create those changes manually. (You can find the description of the msdb system database in Chapter “System Environment of the Database Engine”.)
Note – You cannot restore a database that is being accessed by users. Therefore, when restoring the msdb database, the SQL Server Agent service should be stopped. (SQL Server Agent accesses the msdb database.)
