Rebuild msdb Database

If the msdb database is damaged and you do not have a backup of the msdb database, you can create a new msdb by using the instmsdb script.

System_CAPS_warning

Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.

1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.

2. Start SQL Server from the command line using the command: NET START MSSQLSERVER /T3608

3608       Prevents SQL Server from automatically starting and recovering any database except the master database.

3. In another command line window, detach the msdb database by executing the following command, replacing <servername> with the instance of SQL Server: SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"

4. using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance.

5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.

6. In a command line window, connect to SQL Server and execute the command: SQLCMD -E -S<servername> -i"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.out"

Replace <servername> with the instance of the Database Engine. Use the file system path of the instance of SQL Server.

7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.

8. Re-apply any service packs or hotfix installed on the instance.

9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.

10. Backup the msdb database.