Problem
My SQL Server MSDB database is flagged as “suspect” what can I do?
When I restarted one of my (thankfully) test instances today I got a message that MSDB was in “suspect” state and needed to be recovered or repaired. What I thought would be a simple exercise in database recovery taught me a few things about the system databases and MSDB in particular.
Solution
Plan “A” – Recover SQL Server MSDB from a Backup
“Sure”, I thought, “I’ll just go get lastv night’s backup and get busy.” Restoring from the backup is my first option because it has the least risk of data loss. Depending on the problem, how often you backup the transaction logs and how active the database has been you can get up to 100% recovery. The process for restoring MSDB is pretty much like restoring a user database. Atif Shehzad covers the process for recovering MSDB from backup here. I’ve done this a few times as practice which helps prevent some of the panic when something goes wrong for real, but when I started NetApp SnapManager (our backup tool) it informed me that it had not been configured on this server… uh oh! I have no backup to recover!
Plan “B” – Grab another SQL Server Instance’s MSDB Backup
So now what? I have an unrecoverable MSDB and no backup to restore. Data loss at this point is a given, my next best option is to restore a backup from a similar instance of the same version/build number over my corrupt ones. For example the test or QA version of a production instance or, in this case, the production version of the test instance. Keep in mind that with MSDB out of commission you will need to restore it using T-SQL, SQL Server Management Studio can’t do a database restore from the GUI without MSDB running! This will not be a surprise when you’ve done a test restore or two.
USE [master]
GO
RESTORE DATABASE [msdb]
FROM DISK = N'C:\msdb.bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE
GO
The version and build needs to match for this transplant to work, a mismatch will result in an error as shown below and it will not restore.
device C:\msdb.bak cannot be restored because it was created by a different
version of the server (10.50.1600) than this server (10.50.2500). Msg 3013,
Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
Had I been able to restore from this backup I would then need to spend some time cleaning up the contents: jobs, schedules, alerts and notifications all need to be set to the correct values! For example, this is a restore from Production to Test, so I want the alerts to go to email rather than my on-call device and I do not want jobs to point to any production resources.
As luck would have it, I’m in the middle of an upgrade cycle so this test instance has a Service Pack applied that its production cousin does not. This is not turning out to be a good day.
Plan “C” – Use a SQL Server Template MSDB Database
Plan “B” has fizzled, I have no backup, and I don’t have a close relative to get a transplant from, I need another option. I want to get the instance up and running and have resigned myself to LOSING ALL OF THE DATA f from MSDB. Back in the SQL Server 2005 days I would to start the instance with trace flag 3608 turned on and run the MSDB install script. Starting with SQL Server 2008 Microsoft changed the system database rebuild process and creates a template for each of the system databases at install time. The templates are saved in “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates” (the path varies by version and install choices, this is the default for SQL Server 2012). By shutting down the instance and replacing the bad MSDB data (msdbdata.mdf) and transaction log (msdblog.ldf) files with the template files I was able to restart the instance without error!
Short of a database backup, you can recreate many objects in MSDB from scripts if you have them. For example, I have a standard mail configuration and set of alerts and operators that I put in every instance I create, they are all scripted and easily run as part of our commissioning process. The script is not intended as a recovery tool, but in this case it will save me a lot of time setting things back up! I can use the scripting function in SQL Server Management Studio to recreate many of the other critical objects from the production instance, tedious to be sure, but probably easier than building them from scratch
Wrap-up
For lack of a backup I lost everything that was in MSDB… jobs, job history, backup history, mail configuration, etc. This would have been a major issue in a production system, but in this case is was a tolerable loss in exchange for having a viable test instance- and I have time to recreate most of it using generated scripts from SSMS. Sometimes we just get lucky, there is just no substitute for a good backup. The very first thing a DBA should do upon installing SQL Server is implement a backup plan, especially for the system databases! They may seem small and insignificant, but as the saying goes “You don’t know what you’ve got until it’s gone”. The next thing is to know what to do with the backup once you have it, a production-down scenario is not when you want to be searching MSSQLTips.com to learn a new skill!
Next Steps
- Making sure your system databases are backed up.
- Practice restoring system databases in a non-production environment!
- Using Snap Manager for SQL Server
