How to recover sql server after data drive crash (loss of master db mdf file)?

Question

C drive is OS drive

D drive is data drive (system and user dbs)

E drive is log drive (system and user dbs)

D drive has crashed. So sql isn't starting as master mdf file is missing.

I could have started the sql server by pointing to the backup of master mdf file, then restore msdb/model/user dbs. But all my backup files are in .bak format.

I have full and log backups of system/user dbs (.bak). How do I extract the master mdf file to startup my sql server?

Maybe restore master on a new test server, then copy that master mdf file to the original sql server. Or would it be appropritae to rebuild system dbs, and then restore master/msdb/model/user dbs?

Answer 1

There are two approaches I've worked with.

1. Rebuild the system databases and then restore master from a recent backup.
2. Get a copy of system databases from system/storage-level backup. If there are, say, volume snapshots of the disk containing system databases, those should be VSS consistent. Get a copy of the master's files from there, and you should be good to go.

I recommend reading Gail Shaw's excellent article, The SQL Server Instance That Will not Start, for a repository of solutions for several kind of startup issues.