Backup and recovery concepts Recovery factors
Individual filegroup differentials
Read-write filegroup differentials, i.e., backups that include differentials on
all the read-write filegroups in a database.
Partial differential filegroups, i.e., backups that include differentials of just
the filegroups that the user selects.
Caution: Microsoft recommends you do not create more than one type of differential backup for the same object.
Caution: NetBackup does not consider differential images in determining recovery staging strategies if more than one type of differential is found for the same object.
See “Staging recovery” on page 58.
A typical backup procedure may use full database, differential, and transaction log backups in ascending order of frequency. For example the full database backup may be taken bi-weekly, the differential may be taken nightly, and the transaction log backup may be made as frequently as every hour for either mission critical or high volume applications.
File and filegroup backups
In addition to full, database differential, and transaction log backups of a database, SQL Server also supports backing up individual filegroups and files as distinct images. Since a filegroup is composed of one or more database files, backing up the constituent files of a filegroup is logically equivalent to backing up the filegroup itself.
Filegroup and file backups would commonly be used in a tightly architected application in which physical disk locations were mapped to logical objects, such as tables and indexes. There are several factors which may lead to the choice to use file and filegroup backups in this type of environment.
Some portions of the database, which may be distinguished by such factors as volatility or mission criticality, may be recognized as having to be backed up more frequently than other portions.
The database may be so large that the time required for a full database backup could not fit in the allocated time window. Thus it may be more viable to do a full backup of one or more files or filegroups on a rotating basis.