Recovering the Master Database from Scratch

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
Advertisements