File Differential Backups
You can create a file differential backup to back up only the data changed since the last file backup. File differential backups can dramatically reduce recovery time by reducing the amount of transaction log that must be restored. In Microsoft® SQL Server™ 2000, file differential backups can be extremely fast because SQL Server 2000 tracks changes made since the file was last backed up. Therefore, the file is not scanned.
Consider file differential backups if:
- You are backing up some files much less frequently than others.
- Your files are large and the data is updated infrequently, or the same data is updated repeatedly.
- You have backed up a read-only file. A recent file differential backup will eliminate the need to apply many log backups to recover the file.
File differential backups can be used only in conjunction with file backups and are only supported by the Full Recovery and Bulk-Logged Recovery models. For more information, see Using File Backups and Selecting a Recovery Model.
To restore a damaged file or files from file backups and file differential 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 file backup of that file.
- Restore the most recent file differential backup for each file restored in Step 2.
- 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.
The procedure for restoring the entire database is similar. The only difference is that all files are restored, and you can recover to a specific point in time or a named transaction.
Information about available backups is contained in msdb. If msdb is unavailable, this information can be obtained from the backup itself.
It is not recommended to use both database differential and file differential backups on the same database.