Summary: master, model, and msdb databases need to be backed up.

1. What needs to be backed up? Master, model, msdb, and distribution if replication is used.

2. Which model should I use? Simple for master and model, full for msdb if necessary (but simple is the default)

3. Backup and restore the master database

1) Frequent full backup

2) Whatever changes the contents in the master database deserves a backup theoretically

3) Restore from a full backup

a) Start the server instance in single-user mode.

· In SQL Server Configuration Manager, click SQL Server Services.

· In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

· On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter, and then click Add.

o For example, to start in single-user mode, type -m in the Specify a startup parameter box and then click Add. (When you restart SQL Server in single-user mode, stop the SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)

· Click OK.

· Restart the Database Engine.

b) Run sqlcmd and executes a RESTORE DATABASE command to restore the master database on the default server instance. For a named instance, the sqlcmd command must specify the -S<ComputerName>\<InstanceName> option. The REPLACE option instructs SQL Server to restore the specified database even when a database of the same name already exists. The existing database, if any, is deleted.

C:\> sqlcmd

1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;

2> GO

c) After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process. Before you restart the server instance, remove the single-user startup parameter.

d) Restart the server instance and continue other recovery steps such as restoring other databases, attaching databases, and correcting user mismatches.

4) Backup and restore the model database: the same as restore from a full backup of a user database

5) Backup and restore the msdb database: the same as restore from a full backup of a user database

The following table summarizes all of the system databases.

System database	Description	Are backups required?	Recovery model	Comments
master	The database that records all of the system level information for a SQL Server system.	Yes	Simple	Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update.
model	The template for all databases that are created on the instance of SQL Server.	Yes	User configurable¹	Back up model only when necessary for your business needs; for example, immediately after customizing its database options. Best practice: We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.
msdb	The database used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. msdb also contains history tables such as the backup and restore history tables.	Yes	Simple (default)	Back up msdb whenever it is updated.
Resource(RDB)	A read-only database that contains copies of all system objects that ship with SQL Server	No	—	The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database. Note: You can perform a file-based or a disk-based backup on the mssqlsystemresource.mdf file by treating the file as if it were a binary (.exe) file, instead of a database file. But you cannot use SQL Server restore on the backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
tempdb	A workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently.	No	Simple	You cannot back up the tempdb system database.
Configure Distribution	A database that exists only if the server is configured as a replication Distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication.	Yes	Simple	For information about when to back up the distribution database, see Back Up and Restore Replicated Databases.

4. Case Study – how to move all of the databases to a new server

Scenario: (a) The original sever was broken, you need to migrate the databases to a new server called sql2005pc, SQL Server is the default instance on this machine; (b) The installation path on the new server is c:\program files\Microsoft sql server\mssql.4\mssql; (c) You have the backup for master, msdb, model, and all of the other user databases. The restore procedures are:

1) Make sure the version of sql server on the new server is the same as that on the original server:

SELECT @@version

2) Start the sql server service in a single user mode on the new server:

run|cmd|net start MSSQLServer /m

3) Use sqlcmd to connect to SQL Sever:

sqlcmd –E –S sql2005pc

4) Restore the master database:

1>restore database master from disk = ‘c:\master.bak’

5) Restart SQL Server service with the 3608 trace flag due to the path change:

net start MSSQLServer /f /m /T3608

6) Connecting to SQL Server now:

sqlcmd –E –S sql2005pc

7) Change the file path for all of the system databases:

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=DATA,FILENAME='C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.mdf')

ALTER DATABASE mssqlsystemresource MODIFY FILE(NAME=LOG,FILENAME='C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.ldf')

ALTER DATABASE msdb MODIFY FILE(NAME=MSDBData,

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf')

ALTER DATABASE msdb MODIFY FILE(NAME=MSDBLog,

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf')

ALTER DATABASE model MODIFY FILE(NAME=modeldev,

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf')

ALTER DATABASE model MODIFY FILE(NAME=modellog, FILENAME='C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\Data\modellog.ldf')

ALTER DATABASE tempdb MODIFY FILE(NAME=tempdev, FILENAME='C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\Data\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE(NAME=templog, FILENAME='C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\Data\templog.ldf')

8) Exit sqlcmd:

exit

9) Stop Sql Sever:

net stop MSSQLServer

10) Start SQL Server normally:

net start MSSQLServer

11) Restore msdb:

RESTORE DATABASE msdb FROM DISK='C:\msdb.bak' WITH MOVE 'MSDBData'

TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf',

MOVE 'MSDBLog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf',REPLACE

12) Restore model

RESTORE DATABASE model FROM DISK='C:\model.bak' WITH MOVE 'ModelData' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modeldata.mdf',

MOVE 'ModelLog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\Modellog.ldf',REPLACE

13) Change the server name

sp_dropserver 'OldServerName'

sp_addserver 'SQL2005PC', 'Local'

On the Journey to be a SQL Server Professional

Backup and Restore of System Databases