Description of restoring file and filegroup backups in SQL Server

Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group. This article discusses some of the important caveats related to restoring files and filegroups.

from:

[@more@]

Transaction Log Backups Are Required

You must use file and filegroup backup and restore operations in conjunction with transaction log backups. After you restore the files, you must restore the transaction log backups that were created since the file backups were created to bring the database to a consistent state. It is not necessary to apply a transaction log backup if SQL Server can determine that the files or filegroups have not been modified after the file or filegroup backup was created.
SQL Server 7.0: This requires that the TruncateLogOnCheckpoint option is not set and that the transaction log backups are created in addition to the database or file and filegroup backups.
SQL Server 2000 and SQL Server 2005: To create transaction log backups, you must use either the Full Recovery or Bulk-Logged Recovery models. For more information on recovery models, see SQL Server Books Online "Selecting a Recovery Model" topic.
NOTE: You must maintain a full set of file backups and covering log backups. A media failure can render an entire database unrecoverable if there is no backup of the damaged file.

It is not possible to stop the recovery of individual files early. For this reason, you must always back up the active transaction log prior to restoring a file backup. If the transaction log is damaged or if you wish to recover the entire database to a specific point in time, you must restore the entire set of file backups before you apply transaction log backups. To minimize the risk of transaction log damage, locate the transaction log on fault tolerant storage.

If the Entire Database Is Lost
You must have backups of every file or filegroup in the database.You must also have 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.
NOTE: If any one of the above conditions is not met, it is not possible to restore the database.

File and Filegroup Backups Must Be Restored to the Matching Database

File and filegroup backups can only be restored to the database to which they belong. You cannot create a new, blank database with the same structure and filenames and then attempt to restore the single file or filegroup backup; you must restore it into the existing database or perform a full database restore in another location. (In SQL Server 2000, there is a new clause added to RESTORE DATABASE for partial database restore operations. See the discussion later in this article on "Partial Database Restore Operations.")

NOTE: Do not attempt to detach the database and then re-attach it if a file in a multiple file database or filegroup is lost. Instead, restore the needed file or filegroup from backup. If the database is detached, the re-attachment fails and you are forced to restore the entire database from backup. This is because database files are matched to the database based on a Global Identifier (GUID). This design is to protect the integrity of the database so that files that do not belong to the database are not intermixed, which would cause serious data integrity issues. Even though you may create a new database with the same filenames, the GUID does not match.

SQL Server does not allow you to attach a single file of a database that contains multiple files. The attach looks for all files that belong to the database during the attach and if it cannot find the files with the corresponding GUID, it fails the attach. Similarly, if you create a blank database with the same filenames and filegroups as the original database, replace some of the files, and then attempt to let SQL recover the database upon startup, recovery fails as indicated by the errorlog. For example:
2000-11-28 13:14:52.88 spid9 Opening file C:MSSQL7dataf2_Data.NDF.
2000-11-28 13:14:53.01 spid9 Cannot associate files with different databases.
2000-11-28 13:14:53.14 spid9 Device activation error. The physical file name 'C:MSSQL7dataf2_Data.NDF' may be incorrect.

Partial Database Restore Operations (SQL Server 2000)

A new PARTIAL clause has been added to the RESTORE T-SQL statement that provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database. Partial restore operations work with database filegroups. For example, you have a database that consists of a primary filegroup, filegroup A and filegroup B. A table that resides on filegroup B is accidentally deleted. If you have filegroup and transaction log backups available, you can restore only filegroup B along with the primary filegroup to regain the deleted table. The RESTORE statement with the PARTIAL clause allows you to restore the subset to a new database or even to a different server. You can then extract and reload the content of the table into the original database.

The primary filegroup is always restored along with the filegroups selected to restore. Filegroups that are not restored are marked as offline and are not accessible. Partial database restore of file backups is not supported.

Note If you are using SQL Server 2005, the initial stage of a piecemeal restore replaces the partial database restore of SQL Server 2000. In SQL Server 2005, the PARTIAL option performs safety checks that are not available in partial database restores. This behavior lets such databases be used as production databases. For more information about piecemeal restore in SQL Server 2005, see the "Piecemeal Restore Scenario (Full Recovery Model) " and the "Piecemeal Restore Scenario (Simple Recovery Model)" topics in SQL Server 2005 Books Online.

For more information on how to perform a partial database restore, see SQL Server 2000 Books Online "Partial Restore Operations" and "RESTORE DATABASE".

Please see the following subjects regarding files and filegroups:

SQL Server 7.0 Books Online topics:

"Physical Database Files and Filegroups"
"Using Files and Filegroups"
"Creating Filegroups"
"Creating File or Filegroup Backups"
"Using File or Filegroup Backups"
"Restoring File or Filegroup Backups"
"File and Filegroup Backup and Restore"

SQL Server 2000 Books Online topics:

"Physical Database Files and Filegroups"
"Using Files and Filegroups"
"Creating Filegroups"
"Using File Backups"
"Files and Filegroups"
"Backing up and Restoring Databases"
"Partial Database Restore Operations"
"Backing up Selected Portions of a Database"

SQL Server 2005 Books Online topics:

"Physical Database Files and Filegroups"
"Using Files and Filegroups"
"Creating Filegroups"
"File and Filegroup Backups"
"Designing Files and Filegroups"
"Backing up and Restoring Databases"
"Piecemeal Restore Scenario (Full Recovery Model)"
"Backing up Selected Portions of a Database"