HELP! HELP! My system databases have disappeared.

Disaster! The SAN array that hosts our system databases crashed. Now what do we do?

Fortunately, the array that hosted the C: drive where SQL Server was installed was intact as was our drives for the data files, transaction logs and backups. Here is how we got our server back up and running. First you will need to copy the ISO of the appropriate version and edition of SQL Server to your VM and mount it.

Next run command prompt as admin and execute the following script changing the drive letter, instance name if a named instance, Windows account to add with administrative access and password to fit your circumstances:

E:\>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="domain\YourLogin" /SAPWD=$0m3P@$$w0rD

Once it finishes rebuilding the system databases it should look similar to the following.

If you look in C:\Program Files\Microsoft SQL Server\...\MSSQL\Data you should now see system data files and transaction logs.

Go to SQL Server Configuration Manager and in the Startup Parameters add the -m flag to start SQL Server in single user mode.

Now in SQL Server Management Studio you can connect a query window, not object explorer, and use the following query to restore master database altering the query to fit your situation. You could also do this from SQLCMD if you prefer. Before running this you need to make sure that new drives with the same drive letters have been created for all system databases.

RESTORE DATABASE master FROM DISK = 'C:\IT\master_backup_2016_09_09_140813_6577087.bak' WITH REPLACE

Once the restore completes it will stop SQL Server. Remove the -m flag. You should also update the location for master database under Startup Parameters and copy the newly restored data and log files to the correct locations. You can now start SQL Server and reconnect SQL Server Management Studio and restore msdb; just be sure the SQL Agent service isn’t running. You can restore model and distribution if needed as well.

You should now be back in business.