By: Eric Blinn
Overview
In this section we will cover questions like: is there a need to script out objects from the master database and can you just use a master database from another server.
Should you script out objects from the SQL Server master database for disaster recovery?
Most of the objects in master cannot be recovered via script. It would be a better choice to take copious backups of the master database and be prepared to restore one in the event of a disaster. One thing you can script out is the logins, check out this article for more details.
Can you copy and replace the SQL Server master database files from one instance to another?
A master database copied from another instance, assuming that instance is from the same or an older major version of SQL Server, will be recognized by the service and SQL Server will attempt to load it. The problem here is that it will attempt to load the other databases, system and user, from the location on the other instance. It’s unlikely those files will exist. The service will surely report a whole host of errors as it attempts to load. It may or may not finally restart the service.
In a situation where the master database is unavailable or corrupt and backups are not reliable, it would be a better choice to get a new, blank master database. To get one, install SQL Server on another machine and take that blank master database to the failing instance. The user databases will have to be attached and the logins recovered, but the service is far more likely to start with the blank master database.
Last Update: 10/8/2019
