Monday, November 7, 2011

Recreate the msdb database in SQL Server

If your msdb goes suspect then you had two choices, either recreate it or restore from backup. 
The first thing you had to do is find the reason, why it goes in suspect mode and take necessary action to stop it from happening again,
Now,If you have a good backup than restore that and you you are through.
If you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. That works as long as the transaction log is not damaged. Now, msdb can't be put into emergency mode. So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the folder paths to suit your installation):
  1. Detach the damaged msdb Database. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
  2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data' directory)
  3. Run the instmsdb.sql script in the 'D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Install' directory
  4. Shutdown and restart the server without the 3608 trace flag

No comments:

Post a Comment