After doing a recent disaster recovery test, I decided to do things differently when restoring a SQL Server from scratch. Hope this helps some of you out.
This still requires a restart btw, but may be quicker/easier depending on what you are trying to accomplish.
Simply restore the Master database to a new database with new MDF and LDF files. You can then stop the SQL Server service, copy the original database files off for safe keeping and then replace the clean ones with the new ones. Example….
Normally you restore Master.BAK a database called Master and to files named Master.MDF and Mastlog.LDF Instead do the following.
- Instead restore to a database named Master2 and files name Master2.MDF and Mastlog2.LDF.
- Turn off SQL Server
- Rename Master.MDF to Master0.MDF
- Rename Mastlog.MDF to Mastlog0.MDF
- Rename Master2.MDF to Master.MDF
- Rename Mastlog2.LDF to Mastlog.MDF
- Turn SQL Server back on.
This is a good way to restore the master database with the least amount of downtime. Some people might want to do the following with the Master and MSDB database to hit the ground running with restoring all the other databases in case of recovering from a server loss or just testing your disaster recovery scenario.