Backup and recovery concepts Overview
SQL Server system database types
A SQL Server instance cannot be backed up as a single entity. The largest granularity of a SQL Server backup is the database. SQL Server has system and user databases. The system databases are:
This is the “brains” of your installation, containing a great deal of the metadata describing your instance. Be sure to retain an up-to-date backup of the master database. In particular, back up the master any time you have made changes to your SQL Server installation, including when you have created or modified other databases. Note that you can only do full database backups on the master. You cannot back up its component files, perform differentials, or backup up its transaction log. Recovery of the master database requires special considerations.
See “Disaster recovery” on page 119.
The msdb is used by the Microsoft SQL Agent for scheduling, alerts, and for recording the backup history. It is amenable to all types of backup providing that it has the full or bulk-load recovery option set.
The model database serves as a template for new databases when the Create Database statement is executed. It is also amenable to all types of backup, providing that it has the full or bulk-load recovery option set.
The tempdb is for temporary databases used by applications. It cannot be backed up and does not appear in the NetBackup for SQL Server backup browser.
Three types of backup operations can be performed on databases:
The database, including all of its component files are backed up as a single image. The log file is included in a full database backup.
Note: The transaction log is not automatically truncated following a full backup. Thus a common practice to preserve disk space is to manually truncate the transaction log following a successful full backup.
Differential All of the changes since the last full are backed up to a single image.