I ran into a situation where multiple servers lost their data and log drives that housed the user and system databases due to a SAN failure. The servers still had valid SQL installs since the system drive was still available. Instead of re-installing SQL Server and service packs/hotfixes from scratch I used the steps below to create a new master database, restore the backed up system databases, and finally restore the backed up user databases. This method was much quicker and easier than uninstalling/re-installing SQL from scratch.

• Copy the SQL installation media to the server
• Open a command prompt and go to the install directory above and run setup /ACTION=REBUILDDATABASE
  /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= /SAPWD=
  • Use your account or an AD group for the SQLSYSADMINACCOUNTS flag. This account/group will have sysadmin rights with the new master database.
  • Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup.
• Start server in single user mode (use –m flag in the SQL server startup parameters in configuration manager) and log in with Management Studio
• Run script to restore database
  • RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;
• Remove single user flag and restart SQL
• Restore model and MSDB
• Restart SQL agent
• Restore user databases