Need to restore MSDB with only the ldf/mdf files

Question

Need to restore MSDB with only the ldf/mdf files

SG-348334

Ten Centuries

Points: 1374
More actions
January 28, 2008 at 3:05 pm

#120226

I lost the backups on one of my servers and need to recreate the MSDB to get the DTS jobs.
I was able to find the ldf/mdf files for the MSDB, but don't have the backup.
Can I just stop SQL, replace the existing ldf/mdf files with the ones from the other server and restart?
Any help would be greatly appreciated.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You do not need to stop your SQL server. What you need to do is only detach/attach stuff when you have both mdf and ldf files.

SG-348334 Ten Centuries Points: 1374 More actions · Answer 2

I don't believe that's an option with the system databases -

Here's the error message I receive -

System databases master, model, msdb, and tempdb cannot be detached.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Special instructions for system databases:

http://msdn2.microsoft.com/en-us/library/ms190749.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SG-348334 Ten Centuries Points: 1374 More actions · Answer 4

Sorry should of mentioned I'm working with SQL 2000.

The article you referred me to is for restoring a backup.

I don't have a backup, just the ldf/mdf files.

Thanks

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

Could you open your master..sysdatabases, modify the column filename to the locations of your mdf and ldf files? Then, restart your services?

george sibbald SSC Guru Points: 104210 More actions · Answer 6

SG, if I understand correctly, you do not have a backup of the msdb databse, but you do have the mdf and ldf files from some time in the past when it contained your DTS packages, yes? presumably you had to do a rebuildm or reinstall?

If so what you are suggesting will work as long a the SQL versions of the msdb 's are EXACTLY THE SAME (i,e 8.00.2187 or whatever)

In which case

stop sql services

move current msdb files somewhere safe out of the way

move the old msdb files in

restart sql

should come up filn, if any problems, stop sql , put original msdb files back

back all system databases up before you start! and afterwards

obviously things like backup history, sqlagent job history will be out of date, but that is of little consequence.

---------------------------------------------------------------------

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

There's actually nothing preventing you from attaching the older copy of MSDB as a user database and getting the info out of the table that stores the DTS packages. The stuff is stored in msdb.dbo.sysdtspackages.

I did a quick search and someone has already beaten me to the punch. Here are some decent instructions on how to get the data.

Of course - this goes along with the standard warning as to hacking around in system tables, especially undocumented ones: have a CURRENT backup, and TEST first....

http://www.sqldts.com/204.aspx

Your mileage may vary.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 8

Could you try to attach your file as a user database with different name, then make a backup, and then restore/overwrite it on msdb?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQL ORACLE (1/29/2008)
Could you open your master..sysdatabases, modify the column filename to the locations of your mdf and ldf files? Then, restart your services?

Modifying the system tables is not recommended on any version of SQL. On SQL 2005 it's impossible.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 10

George sibbald suggested method will work fine, stop sql, backup system db folder and restrore msdb files and start and see if it works, otherwise stop and replace whole folder with backup and start sql server.....this is tested method so dont worry

here u mentioned to have msdb mdf/ldf but u didn't mention about master mdf/ldf, if you have both then just replace with above method and things will be fine....

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 11

You can detach msdb by using trace flag 3608 but obvioulsy this means shutting down your server and restarting it with this trace flag.

If you can't recover msdb you can rebuild it by starting with the above trace flag moving your knackered msdb files and running instmsdb.sql which can be found in an install directory somwhere sql server program files directory. Then restart without the trace flag.

Paul Randall one of the storage engine team blogged about this a while back so you may want to find his post

hth

David

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 12

GilaMonster:

I agree with you that modification of system databases is not recommended, generally speaking.