How to restore system databases

How to restore system databases

System databases are less likely to be affected by system failures however, you may need to restore them at some point so you must back them up. Not all system databases can be recovered in the same way.

master

The master database holds all system level configurations. SQL Server requires the master database before it can even run. If the master database becomes corrupt, you must first create a temporary master database, then replace it with a backup.

To obtain a temporary master database you can:
a) Use SQL Server setup program to rebuild the system databases (after which you will need to restore all as all will be rebuilt
b) Use a file-level backup of the master database files to restore the master database. This file-level backup must have been taken when the master database was not in use (ie when SQL Server was not running)

If the GUI interface isn’t working, you must then restore master database through sqlcmd using:

RESTORE DATABASE master
FROM ‘D:\AWsys.bak’
WITH REPLACE

This should be done in single-user mode (-m flag in command-line)
Check Books Online for more info on this subject

model

The model database is the template for all databases created on the SQL Server instance. When this is corrupt, the instance of SQL Server cannot start.

In the case of a corrupt model database, the instance must be started with the –T3608 trace flag as a command-line parameter. This only starts the master database. After this, the model database can be restored using RESTORE DATABASE.

msdb

The msdb is used to schedule job and alerts, and recording details of operators. It records history, including backup and restore operations. If msdb become corrupt, SQL Server Agent will not start. msdb can be restored like user databases, then the SQL Server Agent can be restarted.

resource

This is a read-only, hidden database. It can however, become corrupt by failures in areas such as I/O subsystems or memory. If so, it can be restored by a file-level restore in Windows or by running the setup program for SQL Server.

tempdb

This is a temporary database. It cannot be backed up, nor does it ever need to be as it is recreated each time SQL Server is started.