Backup Types

SQL Server provides four kinds of backups:

• Full database backup. If the database is primarily a read-only database, full database backups may be sufficient to prevent data loss. A full database backup serves as your baseline in the event of a system failure. When you perform a full database backup, SQL Server:
  • Backs up any activity that took place during the backup.
  • Backs up any uncommitted transactions in the transaction log file. SQL Server uses the portions of the transactions log file that were captured in the backup file to ensure data consistency when the backup is restored.
• Differential backup. Perform a differential backup to minimize the time that is necessary for restoring a frequently modified database. Perform a differential backup only if you have performed a full database backup. In a differential backup, SQL Server:
  • Backs up the parts of the database that have changed since the last full database backup. SQL Server compares the log file sequence number (LSN) on a page to the synchronization LSN of the last full database backup. When performing a differential backup, SQL Server backs up extents, rather than individual pages. An extent is backed up when the LSN on any page in the extent is greater than the LSN of the last full database backup.
  • Backs up any activity that took place during the differential backup, as well as any uncommitted transactions in the transaction log file.
• Transaction log file backup. Back up the transaction log files to record any database changes. Use transaction log file backups in conjunction with full and optional differential backups to restore data up to the minute the transaction log file was backed up. Transaction log files are automatically backed up when you perform a full backup of a database file. You must perform a full database backup at least once, before backing up the transaction log file. A full database backup is required to restore a transaction log file backup. You will need the corresponding database backup to restore a transaction log file backup. You cannot backup transaction log files when you use the Simple Recovery model. For information about the Simple Recovery model, see SQL Server Books Online. In a transaction log file backup, SQL Server:
  • Backs up the transaction log file from the last successfully executed BACKUP LOG statement to the end of the current transaction log file.
  • Removes previously backed up entries from the transaction log file and backs up the active portion. The active portion of the transaction log file starts at the point of the oldest open transaction and continues to the end of the transaction log file.
• Database file or filegroup backup. In addition to performing full and differential backups, you can back up individual database files and filegroups. When you back up individual database files or filegroups, you must perform a transaction log file backup in order to make the restored files consistent with the rest of the database. You can use this option to specify up to 16 files or filegroups. When you backup individual files or filegroups, SQL Server:
  • Backs up only the database files that you specify in the FILE or FILEGROUP option. For information about the FILE and FILEGROUP options, see SQL Server Books Online.
  • Allows you to back up specific database files instead of the entire database.

For information about database file or filegroup backups, see SQL Server Books Online.

Important

• If you experience a database failure, use the NO_TRUNCATE option to backup the transaction log file. Using this option backs up all recent database activity. The NO_TRUNCATE option causes SQL Server to save the entire transaction log file (everything that has happened since the last BACKUP LOG statement), even if the database is inaccessible. Completed transactions are not purged from the transaction log file, and you can recover data up to the time when the system failed. For more information about the NO_TRUNCATE option, see SQL Server Books Online.

Copyright © 2005 Microsoft Corporation.
All rights reserved.