Search This Blog

SQL Server System Databases Backup


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…

The following shows the system databases and how they are used. Since this data is only stored in these databases, it is key to back up these 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.

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.

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

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

As we need to perform file-based backups for the Resource database files, we 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
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

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 DATABASE Distribution 
TO DISK = 'E:\SystemDatabaseBackups\Distribution.BAK' 
WITH INIT
GO
BACKUP DATABASE ReportServer 
TO DISK = 'E:\SystemDatabaseBackups\ReportServer.BAK' 
WITH INIT
GO
BACKUP DATABASE ReportServerTempDB 
TO DISK = 'E:\SystemDatabaseBackups\ReportServerTempDB.BAK' 
WITH INIT
GO
BACKUP DATABASE Model 
TO DISK = 'E:\SystemDatabaseBackups\Model.BAK' 
WITH INIT
GO
BACKUP DATABASE Master 
TO DISK = 'E:\SystemDatabaseBackups\Master.BAK' 
WITH INIT
GO
BACKUP DATABASE MSDB 
TO DISK = 'E:\SystemDatabaseBackups\MSDB.BAK' 
WITH INIT
GO
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
  • Building your knowledge about the system databases is important to have a better understanding of how SQL Server works internally.
  • Although system databases are normal databases, use caution when working with them due to the potential impact a change could have across the instance.
SQL Server TEMPDB 
Every instance of SQL Server has a single TempDB system database which is shared by all other databases on the same instance. Since SQL Server 2005, TempDB has been used more and more with every new SQL Version.
TempDB is used for the following purpose:
  1. User Objects:
      1. Local and Global Temporary Tables and their indexes
      2. Table Variables
      3. Temp Procedures
  2. Internal Objects:
      1. Work tables for Hash operations and aggregates
      2. Work tables for DBCC CHECKDB and DBCC CHECKTABLE
      3. Work files needed for SORT, GROUP BY, ORDER BY, UNION operations
      4. CTEs: Common Table Expressions
      5. Storing Temporary LOB storage: If they won’t fit in Memory
      6. Work tables for processing Service Broker objects
  3. Version Store:
      1. Online Index Operations
      2. MARS- (Multiple Active Result Sets)
      3. Snapshot Isolation
      4. Read Committed Snapshot Isolation
Few important restrictions we have on TempDB:
  1. Cannot remove PRIMARY data file or log file
  2. Cannot rename or drop TempDB database
  3. Cannot make the TempDB OFFLINE
  4. Cannot add, remove or rename FILEGROUP
  5. Cannot change the Collation , default is Server Collation
  6. Cannot change the owner of the TempDB, its owner is always dbo.
  7. Cannot BACKUP or RESTORE TempDB