The files or filegroups, or combinations of files and filegroups, in a database can be backed up and restored individually. Individual files and filegroups can be restored from database backups, allowing recovery from media failure, which affects only a subset of the files in a database.
A file or filegroup backup is usually created only if there is not enough time to create a backup of the entire database. For example, if a database backup takes three hours to create, but only two hours per night are available to create backups, half of the files can be backed up one night and the other half of the files can be backed up the next night.
Using file or filegroup backups can increase the speed of recovery by restoring only the damaged files or filegroups. If the parts of a database that need to be restored are fully contained within a subset of the files or filegroups in the database, only those files or filegroups need to be restored. This is used primarily for large databases in time-critical environments.
This can reduce the time required to restore a database in cases where only a known portion of the database needs to be restored. For example, if a database is comprised of several files with each file physically located on a different disk and one disk fails, only the file on the failed disk needs to be recovered without having to restore the rest of the database.
However, when restoring a subset of the database, it is also necessary to apply transaction log backups created since the file or filegroup backups were created to bring the database to a consistent state. For example, after restoring the individual file in the previous example, each transaction log backup created since the file was backed up must be restored, otherwise the database will not be consistent (unless no changes have occurred in the database since the file was backed up).
Therefore, to make use of file and filegroup backups, transaction log backups must be created as well.
Individual files or filegroups can be restored from either a database backup or a filegroup backup. This means that you can use database and transaction log backups as your backup procedure, and still be able to restore individual files or filegroups from the database backups.
| Placing Indexes on Filegroups | Placing Tables on Filegroups |
| Files and Filegroups |