Does SQL Server re-create system databases if they are lost/missing?

Question

We have a third-party server backup tool running every 24 hours, which backs up everything (files and database in preparation for a bare-metal restore). After much investigation, it emerged that the tool used non copy_only backups, thus destroying the log chain on both the system databases, and our own.

(I've been researching whether the system databases should be backed up, and the consensus is generally yes because of the data they hold concerning SQL Server in general (e.g. users, agent jobs, etc). This isn't so much about their contents, but what would happen if they were missing after a restore because of the situation I describe).

To stop this crime, I've disabled the tool performing database backups, so now it only does file backups (except the MDF/LDF files). I then have scheduled SQL Agent to run backups every hour and I then sent them to my private cloud.

In the event of complete server failure, and a bare metal backup was required, I expect that the databases wouldn't be there when SQL started back up, as they weren't backed up/restored by the tool (although I have them safely backed up).

What actually happens at this point? Does SQL Server re-create the system databases, which I then can restore over? Or, does it just refuse to start up and I'm heading for trouble?

Answer 1

Answering the initial question

No.

Providing a solution

The procedure of restoring the system databases is explained in the following Microsoft Article:

Reference: Rebuild System Databases (SQL Server 2012)

Prerequisites

1. Ensure you have a clear documentation of the old system:
   • SQL Server Instance Configuration
   • Service Packs and Hotfixes
   • Current location of database files

Restore steps

2. Rebuild the system databases:

   • Insert SQL Server 2012 installation media
   • Perform a rebuild using the following command:

     Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

     Replace the relevant information with the values for your instance.

3. Verify that the installation was successful by consulting the setup summary file:

   C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Logs
   

4. Restore the most recent backups of the system databases: master, msdb, (model):

   • When restoring the master database start the SQL Server instance from a DOS prompt with the -m parameter.
     Reference: Restore the master Database (Transact-SQL) for more details

   • When restoring the msdb database start the SQL Server instance with the Trace Flag 3608:

     Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.

     Reference: Trace Flags (Transact-SQL)

5. Rebuild the ressource database:

   • Launch SQL Server 2012 setup program from installation media
   • Click Maintenance and then Repair
   • Select your Instance
   • On the Ready to Repair summary page click Repair

6. Restore your User databases

Please read

Important Notice
If you never perform a restore, you will never know if you backup (procedure) works. Test and test again.

Answer 2

Finally found an answer here, of which there may be a better one, but this will do while I continue researching...

SQL will not re-create the databases automatically. The following is one process to re-create them.

In case when the master database is not available, use the following steps to rebuild the master database:

Navigate to the directory where the SQL Server is installed, usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.

Note: On my 2012 install this directory was actually C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf

Open the Template Data folder and verify that there is a copy of the system databases created automatically during the original install. Copy the missing files and paste them into the Data directory (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).

Note: On my install, this was C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf

Note: Ensure that the default filters for SQL are not added, as these filters can cause the necessary files to get excluded. For more information on the default SQL filters, see Microsoft SQL Server, Content, Filters, and Regular Expressions - Best Practices.

From the command prompt, run Services.msc In the Services window, right-click the SQL Server instance and select Start.

Answer 3

_{Community wiki answer:}

Yes, you should always back the system databases up. This is an essential part of any disaster recovery procedure, which every production server should have documented and tested.

That said, some folks find it a bit less painful to script out the objects they need from system databases and then run the scripts where they'll be going, rather than restoring over system databases.