Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to recover a suspect msdb database in SQL Server


By:   |   Last Updated: 2014-03-21   |   Comments (22)   |   Related Tips: More > Disaster Recovery

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.

management studio
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.

Msg 3168, Level 16, State 1, Line 2 The backup of the system database on the 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!

templates

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


Last Updated: 2014-03-21


get scripts

next tip button



About the author
MSSQLTips author John Grover John Grover is a 25-year IT veteran with experience in SQL Server, Oracle, clustering and virtualization.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 30, 2018 - 8:17:54 AM - Dimka Back To Top

 plan B is better for me

 


Wednesday, January 11, 2017 - 3:16:37 PM - Bill Back To Top

 Great tip for the template database files, saved the day!

 


Monday, January 02, 2017 - 3:56:23 PM - PT Back To Top

Hurray for plan C. All other articles I found said "steal msdb from a working computer" well that's lame if you already have a great copy in the Template directory! Thank you so much! Saved me a lot of extra time trying things that don't work 

 


Thursday, November 24, 2016 - 4:38:11 PM - Pepe Back To Top

Plan C saved my life. Of course, plus other 3 or 4 things...And my server is simple.

Thanks. Very.

Many people is not backing up msdb !

 

 


Saturday, April 30, 2016 - 5:23:53 AM - CT .TAN Back To Top

Thanks a lot. Your solution is great. Plan C helps. Just one thing to add. The existing database still exist. I just execute plan C, it look like new database. Once i refresh the server connnection object, all old database appear back again. 


Wednesday, December 09, 2015 - 11:59:00 AM - Anju Back To Top

Plan C worked for me.. after whole day of hit and trials.. thanks


Wednesday, November 25, 2015 - 3:17:00 AM - Thae Thae Back To Top

Thanks for your plan 'B'.


Tuesday, November 24, 2015 - 2:12:22 PM - asap Back To Top

Plan C worked for me after 3 hour googling for it !!! THANK YOU A LOT


Wednesday, September 16, 2015 - 2:01:35 AM - sagaya Prabhu Back To Top

Thanks a lot you plan B is work for me

 

 


Monday, April 06, 2015 - 1:54:48 PM - John Grover Back To Top

Bharath-

Thanks for the note but the credit for plan "A" really goes to Atif Shehzad.


Saturday, April 04, 2015 - 8:30:35 PM - bharath Back To Top

Plan "A" - Recover SQL Server MSDB from a Backup helped a lot .. Thanks a lot 
 


Monday, September 22, 2014 - 10:30:40 PM - Mark B Back To Top

John,

You're a life saver! This has saved me from reinstalling MSSQL. Thank you for posting the solution.

 

Mark


Monday, June 23, 2014 - 3:27:53 PM - John Grover Back To Top

Richard,

It was not a case that the backups had been removed, they simply never existed! In the case of a user database I agree that the backup used to initially create the test database may have lingered in a recycle bin somwhere. For msdb we use the one created at install time and add jobs, etc as needed. Since this is a mutli-application system we can't just copy over msdb every time we add a new app!

For those who have a single application running on their instance this is a very good point, though, somewhere in the dusty corners of your system may be a .bak file that will save you some trouble. Unless, of course, you like the dramatic approach ;)

-John

 


Monday, June 23, 2014 - 1:39:27 PM - Richard Granucci Back To Top

So, you had NO backups on the test system at all?  Seems somewhere down the road you must have had a .bak lingering about that you used to create the TEST instance. 

 

So, I would expect you needed to do a bit more searching anyway.

1. Recycle bin

2. Server backups [not SQL but other backups]

then try the more dramatic approach.   Even network disks can have a 'recycle' area....depending.


Friday, May 09, 2014 - 5:31:45 PM - Chaf Back To Top

Just wanted to say thank you for a very useful post.  Had a msdb in suspect mode and windows updates had be run and upgraded the version.  Using your template was short and quick and resolved my issue.  Thank you again


Monday, April 07, 2014 - 1:11:43 PM - John G Back To Top

Jimmy

While I can't speculate on why the database is going suspect I can offer a few ideas. First make sure both instances are the same version and edition, right down to the patch level. Second, if you can, take the source database offline and copy all the data and tlog files to the destination server instead of doing a backup/restore. It's harder and messier that way but may help. Third, if you can identify a point in time where the source database was not suspect you could try using a backup from that period if you have one. Without more information I would lean towards the first suggestion since you are post upgrade and may have patched or upgraded your SQL Server as part of the process.

I hope that is helpful.

John G


Monday, April 07, 2014 - 10:25:45 AM - Jimmy Murphy Back To Top

I have a user database that is going suspect.  The issue that I am having is that when you restore a backup of the database to a report server and try to place it in read only mode it goes suspect.  We just upgraded our GP dynamics and we think that may be the issue but not really sure.  What would you suggest in this scenario?


Thursday, April 03, 2014 - 10:48:29 AM - Markus Back To Top

This is one reason why when I initially install/configure and also after I patch a SQL Server i will stop all of the services and make a copy of the master, model, msdb .mdf and .ldf files.  That way, if all else fails i can quickly get back to a starting SQL Server instance.  When we have an outage I will also sometimes make a copy of them as well.


Tuesday, April 01, 2014 - 2:39:50 AM - Mark Willium Back To Top

Hi John,

Thanks for sharing this tips!!

I was aware with recovery of SQL server user database from suspect mode but not with system database. Now I am able to recover msdb database too.


Friday, March 21, 2014 - 12:49:21 PM - Jeff Bennett Back To Top

John, 

We have had similar experiences in the past year, and eventually tied these occurrences to Microsoft security patches being implemented on the server by our server admin team.      We eventually found articles to prove this.   http://support.microsoft.com/kb/2494088.    I believe the fix is quite simple and does not require a restore or rebuild, BUT...you have to know about the simple fix, otherwise it just looks like a system db failure with no explanation.    MS11-049: Description of the security update for SQL Server 2008 R2 GDR: June 14, 2011.  

Thanks for the write-up.

Jeff, St. Louis, MO, USA


Friday, March 21, 2014 - 11:06:32 AM - bass_player Back To Top

One thing to note is that if you are attempting to go with Plan C, you can still recreate the msdb database using the install script as outlined in this Microsoft documentation


Friday, March 21, 2014 - 10:30:02 AM - Bill Back To Top

Nice tip on the templates for system databases, I wasn't aware that they existed.


Learn more about SQL Server tools