Backing up System Databases

System databases contain information about user databases as well as meta-data about SQL Server, SQL Server Agent, jobs, alerts, DTS packages and more. Therefore, it is crucial to have valid backups of the following databases:

  1. Master
  2. MSDB
  3. Model
  4. Distribution (if using replication)

Another system database, tempdb, is rebuilt each time SQL Server is started. Therefore tempdb does not need to be backed up. The Model database serves as a blue-print for creating new user databases. This database should not have any activity associated with it, other than the database administrator adding or removing objects required in all user databases. Therefore, you should typically use the SIMPLE recovery model for the Model database and only perform full backups for it.

Read more of this post

Advertisements

Simple script to backup all SQL Server databases

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of“DBnameYYYDDMM.BAK”.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Summarizes All of The System Databases.

SQL Server maintains a set of system-level databases, system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update. The system databases that you must always back up include msdbmaster, and model. If any database uses replication on the server instance, there is a distribution system database that you must also back up. Backups of these system databases let you restore and recover the SQL Server system in the event of system failure, such as the loss of a hard disk.

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 configurable1 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 Microsoft SQL Server 2005 or later versions. No The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database.

NoteNote
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 Resourcedatabase 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.
Configuring 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 thedistribution database,