If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obv...
If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only.
Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.
Now you'd hope that 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. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss)in emergency mode). Nope, 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 directory paths to suit your installation):
- Detach the damaged msdb. 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 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldfin the
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
4.Shutdown and restart the server without the 3608 trace flag
This works on SQL Server 2000 as well.
Hopefully you'll never have to do this but if you ever do, let me know how it goes for you.
COMMENTS