Restoring a File or Filegroup

By specifying a file, filegroup, or list of files or filegroups, you are instructing SQL Server to restore only those files or filegroups. This allows you to restore only the damaged part of the database, which could vastly improve your restore time. Consider a 200GB database spread over ten 20GB drives . If only one drive fails, why restore all 200 gigabytes? Restore only the file or files on the failed drive. Your recovery time will be approximately one-tenth the time of a full restore.

If you want to use file or filegroup restore you must be doing transaction log backups. As the restored file will be out of sync with the other files, all transaction log backups from the time the file or filegroup was backed up must be applied to bring it up to date. However, even though all the logs must be read, SQL Server is smart enough to only apply transactions that affect the restored files, and thus speed up the log restoration as well.

If you spread your tables and their associated indexes across multiple files or filegroups, then these must be backed up and restored as a single entity.

TIP

A common misconception is that to do a file or filegroup restore you must be doing file or filegroup backups. It is perfectly acceptable to specify a full database backup as the source of the file or filegroup restore.