Database backups

The following types of backup operations can be performed on databases:

Full

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.

Transaction log

Transaction log backups are only available for the full and bulk-load recovery options. In this operation, the inactive portion of the transaction log is backed up. Four options are available when you select transaction log backup:

  • Back up and truncate transaction log.

  • Back up transaction log, but do not truncate it.

  • Truncate the transaction log, but do not back it up. (SQL Server 2000 only)

  • Back up and restore tail log. (SQL Server 2005 only)

The third option is not a backup at all. It only provides a convenient way to truncate the transaction log for you. It is typically used after a full database backup. The last option is a backup but it does not create a permanent backup image. A typical use of this type of transaction log backup is: after a page-level restore when the database is recovered, but all of the filegroups have not been brought back on line. By backing up and recovering the tail end of the log, SQL Server is able to bring the database back to a usable state.