Imagine, you lose the disk where your master DB is located on. It can happen to the best of us. Today it happened to me.
Luckily, we had a copy of all DB files. Don’t ask why.
This is what needs to be done:
- Stop panicking; SQL Server is not sensitive to emotions
- Copy all system DB files to a new location. Don’t bother copying tempdb files as these will be created from scratch.
- Adapt the startup parameters of the SQL server service to the new location of the master DB files
- Start SQL server in single user mode (sqlservr -c -m -f)
- Connect using SQLCMD and alter the mssqlsystemresource DB to use the new location of its DB files
- Restart SQL server in single user mode
- Connect using SQLCMD and alter model, msdb and tempdb to use the new location of its DB files
- Stop SQL server and start in full access mode.