Restore System Databases from MDF/LOG files

Question

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.

Answer 1

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

Answer 2

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