Complete, differential, and log backups in SQL Server

Applies to: SQL Server 2000, SQL Server 2005

Database backups are at the core of any SQL Server disaster recovery planning for any production system. Backups may be used to provide a means of recovery to a point-in-time when the database was last operational. Microsoft® SQL Server™ provides several types of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the recovery requirements. It is highly recommended that all SQL Server databases be backed up periodically.

SQL Server backup media

A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup.

Types of backups in SQL Server

SQL Server provides several different kinds of backups including Complete, Differential, Transaction Log, and File(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type

Performing a complete database backup

A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored. A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged. It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS/SSIS packages or scheduled jobs, adding and removing linked servers, etc.

Backup syntax

BACKUP DATABASE Northwind
TO DISK = 'c:\backups\northwind.bak'
WITH INIT

Restore syntax (Same database)

RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bak'

Restore syntax (New database and/or server)

RESTORE DATABASE Northwind_new
FROM DISK = 'c:\backups\northwind.bak'
WITH MOVE 'northwind' TO 'c:\new_location\Northwind_new.mdf'
     MOVE 'northwind_log' TO 'c:\new_location\Northwind_new_log.ldf'

Performing a differential database backup

A differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases. Differential backups have several limitations including the following:

• They do not provide point-in-time restore capabilities
• They may only be restored after a complete database backup is restored
• They may not be performed on the master database

Backup syntax

BACKUP DATABASE Northwind
TO DISK = 'c:\backups\northwind_diff.bak'
WITH INIT, DIFFERENTIAL

Restore syntax (Same database - Note that a complete database backup is restored first using WITH NORECOVERY)

RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bkp'
WITH NORECOVERY

RESTORE DATABASE Northwind
FROM DISK = 'c:\northwind_diff.bkp'
WITH RECOVERY

Performing a transaction log backup

An SQL Server database consists of two components: data file(s) and transaction log file(s). A transaction log captures the modifications made to the database. A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number (LSN). Log records that belong to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional replication is configured for the specific database.

A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error: "This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log". If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:

• The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated
• Another transaction log backup was performed between the previous successfully restored backup and the one generating this message
• The transaction log was manually truncated between the two backups
• The database was in Bulk-Logged recovery model and non-logged operations were performed
• Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database

Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. You can also perform a restore to a named transaction with transaction log backups.

Backup syntax

BACKUP LOG Northwind
TO DISK = 'c:\backups\northwind_log_1.bak'
WITH INIT

Restore syntax (Same database - Note that a complete database backup is restored first using WITH NORECOVERY, then a sequence of transaction log backups)

RESTORE DATABASE Northwind
FROM DISK = 'c:\backups\northwind.bkp'
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = 'c:\northwind_log_1.bkp'
WITH RECOVERY

Performing file(s) and filegroup(s) backup

Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.