How to recreate a damaged msdb Database

I just face this problem in one of my projects, MSDB database was damaged and I did not have any backup of it. After Googling a long time, I found many links with some true and false tips. Finally I managed to find the correct way and wanted to share it with you.

There is one important aspect to mention here, if you recreate a new MSDB database using the method I will describe here, this will eliminate all the information stored in MSDB such as jobs, alert, maintenance plans, Database Mail… Find what the MSDB is used for in this MSDN article.

1.       Stop all services connecting to the Database Engine (SQL Server Agent…) and all of your custom applications that use SQL Server as a database.

2.       Start SQL Server from the command line using the command:

NET START MSSQLSERVER /T3608 For more information about Trace Flags (T3608 stops starting auto-recovering database), you can visit http://msdn.microsoft.com/en-us/library/ms188396.aspx.

3.       Detach the MSDB database by executing the following command:

SQLCMD -E –S[servername] -dmaster -Q"EXEC sp_detach_db msdb" where [servername] is the instance of your SQL Server.

4.       Using 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. This will apply back the default trace flag.

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\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER \MSSQL\Install\instmsdb.out" where [servername] is the instance of your SQL Server. Note that the path can be different, more particularly the folder MSSQL10_50.MSSQLSERVER can have a different name.

7.       Open the instmsdb.out file (with any text editor program) and check the output for any errors.

As mention above, creating this new MSDB remove all your information such as jobs, alert… To finalize your restore, you need to recreate user content stored in the old MSDB

Also note that service packs or hotfix should be reinstalled on the instance.