Using File Backups
The files in a database can be backed up and restored individually. Doing this can increase the speed of recovery by allowing you to restore only damaged files without restoring the rest of the database. For example, if a database is comprised of several files physically located on different disks and one disk fails, only the file on the failed disk needs to be restored.
File backup and restore operations must be used in conjunction with transaction log backups. For this reason, file backups can only be used with the Full Recovery and Bulk-Logged Recovery models. For more information on recovery models, see Selecting a Recovery Model.
File backups offer these advantages:
- Recovery from isolated media failures is faster. The damaged file or files can be quickly restored.
- File and transaction log backups can be created simultaneously, allowing you to maintain regular log backup schedules.
- File backups allow greater flexibility in scheduling and media handling. For example, for very large databases, full database backups can become unmanageable.
This flexibility also is useful for large databases that contain data with varying update characteristics.
To maximize these advantages, consider your data layout and usage patterns. It is recommended that you:
- Back up frequently modified data often.
- Back up infrequently modified data less often.
- Back up read-only data once.
Note When restoring a file backup, you must roll forward the transaction log to ensure the file is consistent with the rest of the database. To avoid needing to roll forward many transaction log backups on files that are backed up rarely, use file differential backups. For more information, see File Differential Backups.
File and filegroup backups are functionally equivalent. A filegroup backup is a single backup of all files in the filegroup and is equivalent to explicitly listing all files in the filegroup when creating the backup. Files in a filegroup backup can be restored individually or as a group.
Only one file backup operation can occur at a time. You can backup multiple files in a single operation, but this may extend your recovery time if you only need to restore a single file, because the entire backup will be read to locate that file.
A complete set of file backups, together with backups of the transaction log covering the time that the file backups were created, is the equivalent of a database backup.
Note Individual files can be restored from a database backup. This means that you can use database and transaction log backups as your backup procedure, and still be able to restore individual files. However, it will take longer to locate and restore a file from a database backup than a file backup.
The primary disadvantage of file backups as compared to database backups is the additional administrative complexity. Care must be taken to 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.
When creating file backups, the transaction log is not captured by the backup operation. Transaction log backups must be created after a file backup is created. After restoring files, you must bring the database to a consistent state by restoring the transaction log backups created since the file backups were created.
- Recovery time can be reduced through the use of file differential backups. For more information, see File Differential Backups.
Restoring File Backups
After restoring files, you must restore the transaction log backups created since the file backups were created to bring the database to a consistent state. The transaction log backup can be rolled forward quickly, because only the changes that apply to the restored files are applied.
To restore a damaged file or files from file backups:
- Back up the active transaction log. If you cannot do this because the log has been damaged, you must restore the entire database.
- Restore each damaged file from the most recent backup of that file.
- Restore transaction log backups in sequence, starting with the backup that covers the oldest of the restored files.
- Restore the backup of the active transaction log created in step 1.
- Recover the database.
Important Microsoft® SQL Server™ requires that files be recovered to a state consistent with the rest of the database. 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.
The procedure for restoring the entire database is similar. The only difference is that all files are restored. File backups can also be used to restore the database to an earlier point in time. To do this, you must restore a complete set of file backups, then restore transaction log backups in sequence to reach the desired time. You can stop at a time or a marked transaction.
For more information on point-in-time recovery, see Restoring a Database to a Prior State.
To back up files and filegroups
SQL-DMOTo apply a transaction log backup
To restore files and filegroups