How to restore the msdb database

To ensure a successful restore of the msdb database, follow these two simple major steps:

  • Make preparations for the restore
  • Restoring the msdb database

Here are some information about the msdb database before we start:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

1 Make preparations for the restore

To prepare for a smooth restore we need carry out these tasks:

  • Get the version of the destination server
  • Get the version of source server on which the backup was created
  • Match the versions for the source and destination servers
  • Ensure exclusive access to the database

1.1 Get the version of the destination server

The versions of SQL Server need to be the same for the source and destination when restoring the msdb or model database. If the versions for the source and destination servers do not match then the restore will fail.

Use one of these methods:

SSMS Object Explorer

Connect to a SQL Server instance through SSMS and find the SQL Server version in object explorer as shown below.

SERVERPROPERTY system function

The SERVERPROPERTY system function can be used for retrieving SQL Server version information to get edition and service pack information.

Using @@Version

The SQL Server version can also be retrieved by using @@Version. It can be used in a simple select statement as shown below

1.2 Get the version of source server on which the backup was created

The best way to do this is to get the information from the backup file itself as shown below using the RESTORE HEADERONLY command.


RESTORE HEADERONLY
FROM DISK = '<backup-location>\<msdb-backup-name>.bak'
GO

1.3 Match the versions for the source and destination servers

In our case we have version 10.50.1617 which matches both the source and destination servers. If this is not the case then apply or remove service packs on the destination server to match the source server. Once both versions are the same then we are ready to go further with the process.

1.4 Ensure exclusive access to the database

Exclusive access for the database is required just like restoring any database, but for the msdb it is slightly more demanding. In the case of msdb we also have to consider the SQL Agent service. If the SQL Server agent service is running then exclusive access can not be achieved.

So for msdb we need to stop the SQL Agent service to make sure we can get exclusive access. This can be done by right clicking on SQL Server Agent and selecting Stop.

2 Restoring the msdb database

Here are the steps for restoring the msdb database:

  • Stop the SQL Server Agent
  • Restore the msdb database
  • Start the SQL Server Agent

2.1 Stop the SQL Server Agent

You can either stop the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Stop

2.2 Restore the msdb database

To restore the msdb database do the following:

  • Launch SQL Server Management Studio (SSMS) or activate SQLCMD from a Command Prompt
  • Issue the following:
    RESTORE DATABASE [msdb]
    FROM
    DISK = '<backup-location>\<msdb-backup-file-name>.bak'
    WITH REPLACE;
    GO
    
    -- for example
    RESTORE DATABASE [msdb]
    FROM
    DISK = 'C:\Programs\SQL Server\MSSQL\Backup\msdb_backup.bak'
    WITH REPLACE;
    GO
    
  • Click Execute (if using SSMS) or hit Enter (if using SQLCMD)

2.3 Start the SQL Server Agent

You can either start the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Start

Congratulations! You should’ve already successfully restored the msdb database.

Advertisements

How to restore the master database

Here are the steps for restoring the SQL Server master database:

  • Set MS SQL Server to Single User Mode
  • Restore master database via SQLCMD
  • Reset MS SQL Server to Multi User Mode

Before we start, here’s some important information about the master database:

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Set MS SQL Server to Single User Mode

To set MS SQL Server to single user mode, do the following:

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Append -m; to the above entry. That is you should now have something like this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

Restore master database via SQLCMD

To restore the master database, do the following:

  • Open a Command Prompt as an administrator from the Programs menu
  • At the prompt, type SQLCMD and press Enter
  • Then type
    RESTORE DATABASE master FROM DISK = ‘backup-path\master.bak’ WITH REPLACE;
  • Prese enter and type: GO

Reset MS SQL Server to Multi User Mode

After you have successfully restore the master database, use these steps to set MS SQL Server back to multi user mode:

  • Return to the SQL Server Configuration Manager dialogue screen that you had open previously
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Remove -m; from the above entry. That is, you should now have something like this now: -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

That’s it! You’ve just successfully restore the master database!