How to recover a suspect msdb database in SQL Server

By:   |   Updated: 2014-03-21   |   Comments (29)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-03-21

Comments For This Article




Thursday, October 17, 2024 - 4:51:27 PM - Ivan Back To Top (92580)
Man, you saved my life. Thanks so much

Saturday, November 27, 2021 - 10:01:50 AM - TC Back To Top (89494)
Thank you so much . Plan c worked for me.

Sunday, January 17, 2021 - 3:05:36 PM - Chad Belote Back To Top (88059)
I have seen this caused by insufficient memory (RAM) on a misconfigured multi-instance cluster. It was fixed by restarting the SQL instance after resolving the memory issue.

Sunday, March 1, 2020 - 1:19:55 AM - Biju Back To Top (84861)

Thank you so much  . Plan B worked for me.

Monday, February 17, 2020 - 2:29:51 AM - BalRaj Back To Top (84564)

Great......Thank you so much sir...

It is very helpful and the Plan C resolve the issue immediately... Thanks

Thursday, November 21, 2019 - 1:17:24 AM - corpseCommander Back To Top (83155)

I made it with Plan A, thanks a lot, although, the databse is not a back up so, Thank you Plan B!

Wednesday, October 16, 2019 - 3:58:34 AM - Tibor Karaszi Back To Top (82792)

Watch out if you use the templats. They have the "install media's" collation, not neccesarily mathing the installed SQL Server's collation.

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

 plan B is better for me

 

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

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

 

Monday, January 2, 2017 - 3:56:23 PM - PT Back To Top (45082)

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 (43841)

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 (41370)

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 9, 2015 - 11:59:00 AM - Anju Back To Top (40223)

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 (39138)

Thanks for your plan 'B'.

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

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 (38690)

Thanks a lot you plan B is work for me

 

 

Monday, April 6, 2015 - 1:54:48 PM - John Grover Back To Top (36836)

Bharath-

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

Saturday, April 4, 2015 - 8:30:35 PM - bharath Back To Top (36829)

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 (34665)

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 (32356)

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 (32355)

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 9, 2014 - 5:31:45 PM - Chaf Back To Top (30710)

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 7, 2014 - 1:11:43 PM - John G Back To Top (29994)

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 7, 2014 - 10:25:45 AM - Jimmy Murphy Back To Top (29990)

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 3, 2014 - 10:48:29 AM - Markus Back To Top (29966)

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 1, 2014 - 2:39:50 AM - Mark Willium Back To Top (29932)

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 (29830)

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 (29829)

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 (29828)

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














get free sql tips
agree to terms