Restoring File or Filegroup Backups

A file or filegroup can be restored from either a database backup or a file backup.

After restoring files or filegroups, a transaction log backup, created after the files or filegroups were backed up, needs to be applied to recover the database to a consistent state. Applying a transaction log backup is not necessary if Microsoft® SQL Server™ can determine that the files or filegroups have not been modified after the file or filegroup backup was created.

For example, a database is comprised of filegroups filegroup_a and filegroup_b. A database backup is created every Sunday night at midnight; filegroup_a is backed up on Monday, Wednesday, and Friday at midnight, and filegroup_b is backed up on Tuesday, Thursday, and Saturday at midnight. Additionally, a backup of the transaction log is created every hour. If filegroup_b needs to be restored on Saturday morning because a table within filegroup_b was damaged due to media failure:

1. Restore the filegroup_b backup from Thursday night.
2. Apply all transaction log backups that have been created since filegroup_b was backed up on Thursday night.

If the entire database is lost, the following is required:

• Backups of every file or filegroup in the database.
• An unbroken transaction log backup chain from the time of the earliest file or filegroup backup to the end of the latest file or filegroup backup. The start of the transaction log backup chain must contain the start of the transaction for the oldest transaction outstanding at the time the earliest file backup was created.

Important If one of these two conditions is not met, it is not possible to restore the database.

If any tables and indexes are created that span multiple filegroups, all filegroups affected by the creation of the index must be restored together, followed by applying the subsequent transaction log backup, otherwise only parts of a table or index would be restored, preventing the creation of the index from being recovered. SQL Server generates an error if any of the filegroups that the table and index spans are missing when a filegroup backup is restored. For example, if a table is created on one filegroup, and a nonclustered index for the table is created on a separate filegroup, both filegroups must be restored as a single unit.

To back up files and filegroups

To restore files and filegroups

To apply a transaction log backup