Previous Topic: How the Create Index Statement Impacts File and FileGroup BackupsNext Topic: Restore Overview

Backup and Recovery Best Practices › Backup Concepts › Backup Considerations › Backup Strategy Recommendations › Partial Backups

Partial Backups

Microsoft SQL Server 2005 introduces a special type of Files-and-FileGroups backup called a Partial Backup. These backups automatically select the Primary FileGroup, along with all other FileGroups that are not Read-Only. If the database itself is Read-Only, then only the Primary FileGroup will be included.

Unlike Files-and-FileGroups backups, Partial Backups can be performed on databases that use the Simple Recovery Model, because all of the data that can be changed is included. If you have a large database that contains a large amount of static data in Read-Only FileGroups, you can use a Partial Backup to reduce the size and time required to perform regular backups. As long as you retain the latest Full Database backup and the database structure does not change, you can use Partial Full and Partial Differential backups without needing to perform another Full Database backup.

Also, you can perform a Partial Restore from a Partial Backup, provided all of the desired FileGroups are contained within the Partial Backup session. You can also perform a Database Restore from a Partial Backup, with the last Database Full backup as a pre-requisite.

Note: When using the Simple Recovery Model, you cannot perform a Database Differential backup after a Partial Full backup, until you first perform another Database Full backup. For all recovery models, if you perform a Partial Full backup after a Database Differential backup, the Partial Full backup will be dependent on the Database Differential backup as a pre-requisite.

Important! In SQL Server 7.0 and 2000, a Database Differential backup is dependent on any Files and FileGroup backups that might be between it and the previous Database Full backup. However, in SQL Server 2005 and later, a Database Differential or Partial Differential backup is dependent on any intervening Full backups, including Files and FileGroups Full and Partial Full backups. The Automatic Selection feature cannot detect this condition. If you mix Files and FileGroup backups with Database Differential and Partial Differential backups in SQL Server 2005, you may need to assemble the restore sequences manually.