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