Ask the Expert

Best practices for backing up the master, model, and msdb databases

What are the best practices for backing up the master, model, and msdb databases?

    Requires Free Membership to View

The value of SQL Server system (Master, Model, MSDB) database backups cannot be underestimated due to the significant amount of information residing in these databases. From an operational perspective, I recommend incorporating the system database backups on the same schedule as the user databases i.e. a daily basis. At a minimum backup the system databases each time a configuration is added, changed or removed relative to a database, login, job, operator, etc. One difference between the between the system and user databases is that only full database backups can be issued, not transaction log or differential. As such, the syntax would be:
USE Tempdb
GO
SELECT GETDATE()
GO 
SELECT @@SERVERNAME
GO
BACKUP DATABASE Master TO DISK = 
'D:Program FilesMicrosoft SQL ServerMSSQLBackupsMaster.bak' 
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK = 
'D:Program FilesMicrosoft SQL ServerMSSQLBackupsMsdb.bak' 
WITH INIT
GO
BACKUP DATABASE Model TO DISK = 
'D:Program FilesMicrosoft SQL ServerMSSQLBackupsModel.bak' 
WITH INIT
GO
SELECT GETDATE()
GO
The backups should be saved locally on a redundant disk drive separate from the online databases and backed up to tape on a regular basis. Additionally, if space is available on a file server or secondary server participating in log shipping, it may be beneficial to copy the system database backups from the primary server to the secondary server.

As valuable as the backups are, without a reliable and regularly tested restore process the backups may be useless. Unfortunately the process to restore a system database, especially master is not as simple as a user database due to the overall platform configurations. Check out restoring the master database from a current backup in SQL Server 2000 Books Online for the details. Good luck!

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: