Q

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?
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

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close