通过

How to recreate the msdb database in SQL Server 2005?

  • 项目

I've just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn't find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL Server and tried it on one of the SQL Server 2005 instances on my laptop to make sure it works. I'd like to share it with everyone to save someone having to do the same.

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):

  1. 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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn' and doing the following: start sqlservr.exe -c -T3608
  2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' directory)
  3. Run the instmsdb.sql script in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall' 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

  • Anonymous
    June 08, 2006
    The comment has been removed

  • Anonymous
    June 08, 2006
    Hmm - I just tried it on my Express instance on my laptop and it worked fine. Are you trying to do it on a RANU instance? I ran:

    sqlcmd /E /S.SQLEXPRESS /Iinstmsdb.sql

  • Anonymous
    June 21, 2006
    Great Information!

    This is what I have been looking for for about 7 months, sql server on win 2000 has been stopped due to bad data base, can not run restore as backup requires sql to be running. catch 22.   As the bad data base is one this business has not used in years, your method is a great tool to use.
     I only have one problem.  I just because responsible to this server and have no documentation.
    I run steps 1 and 2 , but the thirdstep of
    "Run the instmsdb.sql script in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall' directory"  is the problem.  How or what do I run it with?   sqlcmd does not seem to be system.

    thanks for any advice you might give.
    regards

  • Anonymous
    June 23, 2006
    Try using 'osql' instead of 'sqlcmd'

  • Anonymous
    June 27, 2006
    Good step by step approach by the Storage engine blog. Will help quite a lot of folks :) ...
    How to...

  • Anonymous
    November 14, 2006
    The comment has been removed

  • Anonymous
    November 14, 2006
    The comment has been removed

  • Anonymous
    November 17, 2006
    Solved the problem by installing MSDE on another computer. Stopping both SQL servers. Copy msdb.mdf and msdb.ldf from good installation to bad installation and started SQL server again. Msdb was now correctly recognized. - thanks

  • Anonymous
    February 02, 2007
    In sql 2005, I also got the same insufficient memory error unable to connect to MSDB DB, I just tried the step 1 with -T3608 Parameter, then tried to stop and restart the sql server.Then i opened the sql server managment studio, it was now able to connect to MSDB DB without no error, before i tried with detach,move and recreate the MSDB DB.So i stopped with Step 1 with trace flag option,then removed the trace flag option in startup parameter.It works fine now. Thanks for the tips. Regards Thenu

  • Anonymous
    April 25, 2007
    It's the second time that I got an ugly message from my SQL Server: Database 'msdb' cannot be opened...

  • Anonymous
    May 04, 2007
    The comment has been removed

  • Anonymous
    July 16, 2007
    I have gone through this exercise. If you have installed SP2 on 2005 and follow the steps above then you will get many problems with Mails. By following the above process, you basically uninstall part of SP2 by running the instmsdb.sql file. The problem is you don't see any real error messages, your emails just stay queued and never sent. A test is stored proc sysmail_delete_profile_sp should contain 3 arguments not 2 if you are on SP2. To fix it, I had to uninstall the 3054 fix + SP2 then re-install SP2 + 3054 and you should be fine. I think Microsoft should provide an updated instmsdb.sql as part of the SP2 installation. Or something similar. I have used -m as well as my argument and & please make sure that nothings is connected to your sql server. Panos.

  • Anonymous
    May 19, 2008
    When i am running the thrid step it is showing in error logs for good backup of msdb database. what to do now

  • Anonymous
    June 06, 2008
    PingBack from http://thought.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/

  • Anonymous
    July 10, 2008
    PingBack from http://winzenz.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/

  • Anonymous
    August 26, 2008
    Here's my findings trying this on SQL Server 2008: You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers... I initially started the instance from the Windows services applet by adding -T3608. That didn't allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb. I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:

  • Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.

  • Bunch of errors when creating various Data Collector objects. This wasn't good, because cleaning up DC was the reason to rebuild msdb in the frist place.

  • 3 errors about sp_configure and -1 wasn't allowed value (two for Agent Xps and one for xp_cmdshell). Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp's when connecting. I tried to explicitly enabling Agent XP's using sp_configure but same error. When connected there's no node in Objects Explorer for Agent. I took this as an indication that Agent isn't healthy. Whether it was me doing something fishy or it isn't as easy as just running insmsdb.sql for SQL Server 2008 - I don't know. But I'm in for a rebuild of system databases. This isn't that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul's initial advice: always backup msdb (also on test machines).

  • Anonymous
    August 26, 2008
    Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about

  • Anonymous
    August 26, 2008
    Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about

  • Anonymous
    September 01, 2008
    PingBack from http://www.easycoded.com/rebuilding-msdb-on-sql-server-2008/

  • Anonymous
    May 29, 2009
    PingBack from http://paidsurveyshub.info/story.php?title=sql-server-storage-engine-how-to-recreate-the-msdb-database-in-sql

  • Anonymous
    June 13, 2009
    PingBack from http://quickdietsite.info/story.php?id=4056

  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=1150

  • Anonymous
    June 19, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24418

  • Anonymous
    July 25, 2010
    For SQL 2005, this work perfect!! Thanks Paul, a very-useful post again. Varund

  • Anonymous
    September 10, 2010
    After trying to run SQL in trace mode as you mention in step 1, we could not detach msdb database, still got message about it being a system database.  tried osql and sqlcmd

  • Anonymous
    February 14, 2011
    Had to do this on a disaster recovery server (w/SQL 2005 Enterprise Ed).  The SAN failed and we lost tempdb and msdb b/c we moved them to SAN drives.  Lesson learned (at least w/msdb).  Worked great; thanks Paul!

  • Anonymous
    August 24, 2011
    Hi Paul.  Dumb question:  Is there a backout procedure for this, in case something goes terribly wrong (e.g., restore [the albeit] MSDB from backup)?

  • Anonymous
    August 29, 2011
    Paul, thanks a lot, Anyway, the explanation you wrote in another thread you specified the use of sp_detach_db "msdb", what is an important part of information. This is the thread: social.msdn.microsoft.com/.../d9a8bd3b-017c-4ff9-b26c-71402c70bfe6

  • Anonymous
    November 13, 2011
    Thanks paul, Article very helpful....

  • Anonymous
    August 12, 2012
    Thanks its recovered but I have setup Distribution server for replication and after replace msdb replication not work.

  • Anonymous
    June 02, 2014
    This is not a correct way to do this one

  • Anonymous
    December 19, 2014
    Very we'll said, just got to step 2 and it doesn't work, instructions are incomplete and the author assumes that everyone has the same level of SQL server experience

  • Anonymous
    August 25, 2015
    It went pretty good in sql 2000. No need to rerun msdb script since it did it by itself when starting sql without the trace flag