Restoring the master database

A damaged master database can prevent the SQL Server from starting and cause other errors. To protect your data if the master database is damaged, you must routinely complete a full database backup of the master database (msdb).

Before you begin

  • Set single-user mode for restore operations.
  • Always keep an up-to-date backup of your master database because the master database contains the system catalog. The system catalog contains important information about the SQL Server configuration.
  • Ensure that you back up the master database after any changes that update system tables. For example, back up the master database after you use any of these statements:
    • ALTER DATABASE
    • CREATE DATABASE
    • DISK INIT
    • DISK RESIZE
    • DISK MIRROR
    • DISK UNMIRROR
    • DISK REMIRROR
    • Various DBCC options such as SHRINKDB
    • System-stored procedure, such as sp_dropremotelogin, sp_addumpdevice, sp_dropdevice, sp_addlogin, sp_droplogin, sp_addserver, sp_dropserver, sp_addremotelogin

About this task

You must complete a VSS restore of the master database while the database is offline. Therefore, you must stop the associated SQL Server instance before you run the restore operation. If you restore an online master database, the operation might fail or disable subsequent VSS backup and VSS restore operations until the SQL Server VSS Writer service is restarted.

If the master database is damaged while a server instance is running, fix the damaged database by restoring a recent full master database backup. If a server instance cannot start because the master database is damaged, the master database must be rebuilt. When you rebuild a master database, all system databases revert to their original state.

Restriction: Microsoft Management Console cannot connect to an SQL Server instance that is started in single-user mode. When the SQL Server instance is in single-user mode, you must use the command-line interface, tdpsqlc.exe to restore the master database.

Procedure

  1. Click Start > All Programs > IBM Spectrum Protect™> Data Protection for Microsoft SQL Server > SQL Client - Command Line.
  2. Start the SQL Server in single-user mode.
  3. Use IBM Spectrum Protect Snapshot for SQL Server to restore the master database. When the master database finishes the restoration process, the SQL Server shuts down and an error message is displayed. The message indicates that the connection to the SQL Server is lost. This loss of connection is expected.
  4. Restart the database engine to restore SQL Server to the typical multiuser mode.
  5. Run the SQL Server setup program to rebuild the master database. When you rebuild the master database, use the same character set and sort order as the master database backup that is to be restored.
  6. Manually reapply any changes to the master database that occurred after the date of the database backup that is used to complete the restore operation.
  7. Restore the msdb database. During the process of rebuilding the master database, the SQL Server setup program drops, and then re-creates, the msdb database. Therefore, you must restore the msdb database with the master database.

Results

After the master database is restored, you can use MMC to back up and restore individual databases that are operating in single-user mode.
Related tasks:
Troubleshooting VSS offline restore of a master database
Setting single-user mode for restore operations