Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK.

I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). I have backups for my other DBs. This is what I found from this blog:

If your msdb goes suspect then you have two choices, if you have backup of database then restore it else you have to recreate it

If you want to recreate it here are the steps to do it

  1. Start the server with trace flag 3608. Detach the damaged msdb
  2. Navigate to the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn and start sqlservr.exe -c -T3608
  3. Move or rename the damaged msdb files
  4. Go to the folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install and run the instmsdb file.
  5. Restart SQL Server without the 3608 trace flag

Has someone done this before? I would like to get opinions or better suggestions before I break things more.

Thanks!

EDIT: My advice to anyone is to treat SQL Express like any production DB. Have rigorous back-up policy and make sure you back-up your system databases.

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 12:35

1 Answer

up vote 3 down vote accepted

You either recreate as above or restore it.

The info above is similar to MSDN, see "Creating a New msdb Database".

Note: if this came from Paul Randal's blog or the SQL Server Storage Engine then you can trust it

share|improve this answer
Thanks for response, but I follow the MSDN instructions, but it wont allow me to detach the database: 'Cannot detach an opened database when the server is in minimally configured mode' – CyMark Sep 24 '11 at 10:08
@CyMark: verify you are following all steps correctly and to the letter – gbn Sep 24 '11 at 10:21
Thanks found my error: I used -c to start the server (Step 1 of Paul Randall's blog). For SQL Server 2008 seems if you use this switch then you cannot detach the msdb. I omitted it and bingo! Thanks again for your time to reply. – CyMark Sep 24 '11 at 10:33
@CyMark: sometimes it's the little things that trip you up. Glad to be helpful. Please accept this answer with the tick on the left :-) – gbn Sep 24 '11 at 10:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.