HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASE

You can create copies of the system DB files while SQL Server is running. 😎

Enhance your daily backup job such that, after the master database is backed up, a script does this:

1. Obtains the name of the backup file or device (from the system tables).

2. Restores the backup as "master2", using WITH MOVE to specify the default physical filenames, but in a different location, such as a subfolder to your backups.

3. Detaches the master2 database.

Those steps will create a ready-to-use copy of the master MDF and LDF files without interrupting the availability of applications (because there is no need to stop SQL Server).

The files will have the same "recovery point" date and time as the BAK file.

When replacement system DB files are needed, there is no need to use special startup modes, no need to reinstall or REBUILD, no need for a ghost installation of SQL Server, no concerns about having exact documentation of the instance configuration or patch level.

NOTE: When SQL Server detaches a database, the file permissions are reduced such that only the run-time identify of SQL Server will have access to them. If you need to use the copies, you will have to take ownership of the files first. You should COPY them back into place (instead of MOVING them) to ensure that they inherit the permissions of the folder they belong in.

You can also do this to the msdb and model databases, to allow you to very quickly restore all three system databases and get SQL Server running again.

A fuller version of this solution would rename the prior copy of the files to *.old until the new copies have been generated, and would start by checking for evidence of a prior failure by checking whether any *.old files exist.

With "the backup folder" being a special location for the copies of the Sysdb files, here is the algorithm I am using:

1. In the backup folder , check for files named *.mdf.old. If found, raise an alert that the prior run did not end cleanly (include a directory listing) and then continue.

2. In the backup folder, delete any files that are named *.mdf.old or *.ldf.old.

3. In the backup folder,

rename *.mdf to *.mdf.old

rename *.ldf to *.ldf.old

4. Check whether any *.old files are missing. if so, raise an alert and then continue.

5. For master, msdb and model ...

5.1 Obtain the name of the current backup files (from the system tables).

5.2 Restore the backup as "<dbname>2", using WITH MOVE to allocate it with the default physical filenames, located in the backup folder.

5.3 Detach the <dbname>2 database.

6. Check whether any MDF or LDF files are missing. if so, raise an alert and then STOP.

7. In the backup folder, delete *.old

8. Check whether any *.old files exit. if so, raise an alert and continue.

The steps that check for files or the lack of files may seem redundant, but they serve an important role: they ensure that you are alerted to problems even if those problems did not raise an error that caused the job to fail. (Ensuring the ability to restore data is a DBAs primary responsibility, so I like to have a high level of confidence that the file copies were created successfully).

This algorithm is also robust enough to handle the situation of a prior run being interrupted.

The text of the alerts can be accumulated until the end (or until a STOP is encountered). That would allow all information to be sent in one alert.

These copies of the system database file are backed up to tape along with the server, so if the server is restored from an older backup, the copies will also be restored as of that date, so they will match the state of the instance's binaries.

I would share my code, but alas, I do not own it. I might write a version of my own to share - perhaps in PowerShell.