Sql-server – Restore System Databases from MDF/LOG files

sql serversql-server-2005

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.

Best Answer

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
Related Question