Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Best Practices Backup System Databases in SQL Server


By:   |   Last Updated: 2009-10-09   |   Comments (6)   |   Related Tips: More > DBA Best Practices

Problem
The role of system databases for the functioning of SQL Server cannot be underestimated due to the significant amount of information which is stored within these databases. System databases which are available in SQL Server 2005 and later versions are Master, Resource, MSDB, MODEL, TempDB, Distribution, ReportServer and ReportServerTempDB.

It's a best practice to create daily backups of all the system databases once all the user databases on the server are backed up successfully.  If not daily, the DBA should at a minimum backup all the system databases each time a server or database configuration is added or modified.  These include Service Packs, Hot Fixes, Cumulative Update, login changes, job changes, operator changes, database configuration changes, SSIS package changes, replication changes, etc...

Solution
The following shows the system databases and how they are used.  Since this data is only stored in these databases this is why it is key to back up these databases.

Primary System Databases

  • master - this holds information about logins and also information about all other databases
  • msdb - this stores jobs, operators, alerts, backup and restore history, database mail information ,etc...
  • model - this is used as a model for all new databases.  If you want certain objects to be in all new databases this is where you configure this information.
  • tempdb - this database is created each time SQL Server starts so there is not a need to back this up

Resource Database

In SQL Server 2005, Microsoft introduced a new system database called the Resource database. The Resource database is a read-only hidden database that contains all the system objects which are included within SQL Server. The DBA needs to perform a file-based copy of mssqlsystemresource.mdf and mssqlsystemresource.ldf files of the Resource database as SQL Server doesn't support backing up the Resource database.

In SQL Server 2005 the Resource database is available in "<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" location and in SQL Server 2008 the Resource database is available in "<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\" location.

Reporting Services Databases

  • ReportServer - will be available if you have installed Reporting Services
  • ReportServerTempDB - will be available if you have installed Reporting Services

Replication System Database

  • distribution - this database will be available when you have configured Replication

Enabling XP_CMDSHELL

As we need to perform file-based backups for the Resource database files, you need to enable xp_cmdshell feature using the sp_configure system stored procedure. The below T-SQL code can be used to enable this feature.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

T-SQL code to backup System Databases

Database Administrators can use the below T-SQL code to backup all of the system databases to SystemDatabaseBackups folder on the E drive.  You will need to change this path for your systems.

This is a simple script that includes code for each database to be backed up.

USE master
GO
SELECT GETDATE() AS 'System Database Backup Start Time'
GO
/* Backup Distribution Database */ 
BACKUP DATABASE Distribution 
TO DISK = 'E:\SystemDatabaseBackups\Distribution.BAK' 
WITH INIT
GO
/* Backup ReportServer Database */ 
BACKUP DATABASE ReportServer 
TO DISK = 'E:\SystemDatabaseBackups\ReportServer.BAK' 
WITH INIT
GO
/* Backup ReportServerTempDB Database */ 
BACKUP DATABASE ReportServerTempDB 
TO DISK = 'E:\SystemDatabaseBackups\ReportServerTempDB.BAK' 
WITH INIT
GO
/* Backup Master Model */ 
BACKUP DATABASE Model 
TO DISK = 'E:\SystemDatabaseBackups\Model.BAK' 
WITH INIT
GO
/* Backup Master Database */ 
BACKUP DATABASE Master 
TO DISK = 'E:\SystemDatabaseBackups\Master.BAK' 
WITH INIT
GO
/* Backup Master MSDB */ 
BACKUP DATABASE MSDB 
TO DISK = 'E:\SystemDatabaseBackups\MSDB.BAK' 
WITH INIT
GO
/* Copy Resource Database Files Using XP_CMDSHELL */ 
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.mdf" "E:\SystemDatabaseBackups"' 
GO
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.ldf" "E:\SystemDatabaseBackups"'
GO
SELECT GETDATE() AS 'System Database Backup End Time'
GO

Here is a listing of these databases and files after they have been backed up.

If you have proper backups of all the system and user databases then you can restore and recover the SQL Server system in the event of a system failure, such as a hard disk corruption.

Next Steps:



Last Updated: 2009-10-09


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 22, 2015 - 1:12:05 AM - VARMA Back To Top

Thank you for your valuble information,

I just want to know can we move resource database files.


Friday, March 09, 2012 - 10:08:24 AM - Qasim Ali Back To Top

Very good post.


Friday, February 05, 2010 - 8:36:26 AM - admin Back To Top

Even if you use a password to protect the backup this does not encrypt the data.

The best solution is to protect the backups, so no one can access them.

In addition, you can create encrypted backups using tools like SQL safe backup from Idera, SQL Backup Pro from Red Gate and LiteSpeed for SQL Server from Quest.


Friday, February 05, 2010 - 8:03:40 AM - manish Back To Top

 Hi Ashish,

 Thanks a lot for the valuable information.

 But I would like to know the method to prevent reading the backups using regular office or notepad utilities. I know I can make it password protected but I don't want to keep any password to open, I just want that the backup files should not be readable.

 Thanks

Manish

 


Friday, October 16, 2009 - 10:55:20 AM - admin Back To Top

Ashish,

I have seen a few different schools of thought on backing up the system databases.  First, is to back them up daily with the remainder of the user defined databases.  Second is to back them up as changes are made.

Have you seen any other approaches?

Do you have any insight into these options?

Thank you,
The MSSQLTips Team


Tuesday, October 13, 2009 - 1:11:58 AM - SankarReddy Back To Top

Ashish,

Good post to remind the best practises. I am surprised how many DBA's don't backup the system databases not only in non-production environments but also in production.

 


Learn more about SQL Server tools