I don’t do this a lot since most of the apps are now a days using Azure SQL using all the benefits it offers. So I thought it would be better to take note of all the steps done for this. If the msdb database is damaged and you do not have a backup of the msdb database (for whatsoever reasons), you can create a new msdb by using the instmsdb script. In this blog post, we’ll go through steps required for the same.

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
3. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.
4. In another command line window, detach the msdb database by executing the following command, replacing with the instance of SQL Server: SQLCMD -E -S -dmaster -Q “EXEC sp_detach_db msdb”
5. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance.
6. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.
7. In a command line window, connect to SQL Server and execute the command: SQLCMD -E -S -i “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql” -o “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out”. Replace with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
8. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
9. Re-apply any service packs or hotfix installed on the instance. Also recreate the user content stored in the msdb database, such as jobs, alert, etc.

Backup the msdb database this time 😉