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.

Database msdb cannot be opened and marked as suspect

If you see the following message when trying to open a database in SQL Server Management Studio:

Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error:926)

Then you need to replace these two files from a backup:

  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBData.mdf
  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBLog.ldf

To do so, follow these steps:

  • Stop the MSSQLSERVER service
    • Either open Services
      • Find SQL Server (MSSQLSERVER)
      • Right click and click stop
    • Or use SQL Server Management Studio (SSMS)
      • Right the database icon and select Stop
  • Once MSSQLSERVER service has stopped, copy and replace the following files in the MSSQL\DATA\ folder:
    • MSDBData.mdf
    • MSDBLog.ldf

If the above files are backups of the current machine’s installation, then Error:926 is now fixed, and no further actions are needed.

However if no backup for the current installation was found, and a backup from another computer was used which has a different install path, then the following error will be shown:

Daily Backup – User Databases.Subplan_1′ cannot be run because the SSIS subsystem failed to load.  The job has been suspended.

Running this query:

SELECT * FROM
msdb.dbo.syssubsystems

will show that the path to the subsystem DLL are incorrect (if the current SQL Server installation is on the D drive but current records points to a different drive or location).

To fix the Job has been suspended error (due to incorrect DLL path), do the following:

  • Delete the existing entries:
    DELETE FROM msdb.dbo.syssubsystems
  • Update the table with new rows pointing to the correct location of the DLLs by:
    EXEC msdb.dbo.sp_verify_subsystems 1