June | 2015 | Applied SQL...

How to Rebuild the MSDB database

I was preparing a migration plan to migrate all SQL Server databases from our existing SAN to new SAN. While doing so I thought of actually doing a small practice on my laptop SQL Server. The user databases moved from the existing location to another location very smoothly. I was very happy. Now was the turn of the system databases. I started with the MSDB database. As recommended in the Microsoft technet article of relocating System DB’s, I was following the steps prescribed. Pretty simple steps. After making the file location change in the master database, I stopped the SQL service, physically moved the files to the new location and then restarted the SQL service. The MSDB database would not come up….In recovery…..

What do I do?…I didn’t even have a backup of the MSDB database. I read few tech net articles that helped me rebuild the MSDB database from scratch. Here is what needs to be done

1) Stop all SQL server services.
2) Start SQL Server from the command prompt using the trace flag T3608. The exact command for this would be : NET START [Name of the SQL service]/T3608. This is what the trace flag does as per Technet.

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.

3) Now we need to detach the MSDB database from the instance. This is what needs to be done for the same. We need to run a SQLCMD command to do the same.
SQLCMD -E -S<servername> -dmaster -Q”EXEC sp_detach_db msdb”
After running this rename the existing MSDB files. Stop and restart SQL Service via the configuration manager.
4) We need to locate the following .SQL file in the install folder of the SQL Server installation files.
5) Open the script in SSMS and execute.