Take the 2-minute tour ×
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.

We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump. The good news is that we have the MDF and LOG files for all the databases, the bad news is that we did not have .bak files for the system databases.

Is there any way to restore the System Databases (msdb, master, model) from the MDF/LOG files, or is there a process I can follow to create a bak file that I can then restore them from?

We have quite a few configuration items that were lost in the recovery. I have been working on getting the mission critical application stuff back in working order and now I'm trying to restore all of our jobs and such.

Any help is greatly appreciated.

share|improve this question
    
If you don't have a backup of the system databases, then you cannot restore them .... –  marc_s Nov 1 '11 at 16:09
add comment

migrated from stackoverflow.com Nov 3 '11 at 23:52

This question came from our site for professional and enthusiast programmers.

2 Answers

msdb and model you may be able to recover data from by following the same process as you would if moving them. Use a test/dev instance for a dry run.

  1. Stop SQL
  2. Start with trace flag 3608 (master only recovery mode) NET START MSSQLSERVER /f /T3608
  3. ALTER DATABASE msdb|model MODIFY FILE( NAME=xyz, FILENAME='filepath')
  4. Stop SQL, move files in to place, restart SQL

KB914171 details one possible error that may be reported and a fix/workaround by:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go
share|improve this answer
add comment

Similar answer to Marks's, but will not need to modify startup parameters or databases. If you are starting over with new hardware you can:

  • install SQL to the same directory path as the previous server (also needs to be the same version and patch level)
  • stop the SQL services
  • backup master, model, msdb mdf/ldf from the new install
  • copy old mdf/ldf files from old instance on top of the new ones
  • restart SQL services
  • if the host name has changed then rename the SQL instance using sp_dropserver 'oldservername' and then sp_addserver 'newservername' , local
share|improve this answer
add comment

Your Answer

 
discard

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