• Article

Full File Backups

This topic is relevant for SQL Server databases that contain multiple files or filegroups.  

Note

For an overview of the various types of backups, see either Backup Under the Simple Recovery Model or Backup Under the Full Recovery Model.

A full file backup backs up all the data in one or more files or filegroups. Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

Note

Full file backups are typically called file backups, except when they are being explicitly compared with differential file backups.

Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database. For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored.

The files in a database can be backed up and restored individually. In a BACKUP or RESTORE statement, you can specify a whole filegroup instead of individually specifying each constituent file. Be aware that if any file of a filegroup is offline (for example, because it is being restored), the whole filegroup is offline and cannot be backed up.

Note

In SQL Server version 7.0 and SQL Server 2000, file backups and differential file backups do not contain log records. A log backup must be explicitly applied to recover their data. Therefore, in these versions, file backups can only be used with the full recovery and bulk-logged recovery models. In SQL Server 2005 and later versions, by default, file backups contain enough log records to roll forward the file to the end of the backup operation.

Advantages and Disadvantages of File Backups

File backups offer the following advantages over database backups:

  • Recovery from isolated media failures is faster. The damaged file or files can be quickly restored.

  • File backups increase flexibility in scheduling and media handling over full database backups, which for very large databases can become unmanageable. The increased flexibility of file or filegroup backups is also useful for large databases that contain data that has varying update characteristics.

The primary disadvantage of file backups compared to full database backups is the additional administrative complexity. A media failure can make a complete database unrecoverable if a damaged file lacks a backup. You must therefore maintain a complete set of file backups, and, for the full/bulk-logged recovery model, one or more log backups covering minimally the interval between the first full file backup and last full file backup.

Maintaining and keeping track of a complete set of these backups can be a time-consuming task that might outweigh the space requirements of full database backups.

Under the full recovery model, at the expense of additional administrative complexity, using differential file backups can reduce the number of log backups that you have to restore . For more information, see Differential File Backups.

Note

File backups of read-only filegroups can be combined with partial backups. Partial backups include all the read/write filegroups and, optionally, one or more read-only filegroups. For more information, see Partial Backups.

Considerations for Backing Up Files

To maximize the advantages of using file backups, consider the layout of the data on disk and usage patterns. We recommend the following guidelines:

  • Back up frequently modified data often.

  • Back up infrequently modified data less often.

  • Back up read-only data one time.

    Note

    Backing up a read-only file or filegroup is the same for every recovery model.

Only one file backup operation can occur at a time. You can back up multiple files in one operation, but this might extend the recovery time if you only have to restore a single file. This is because to locate that file, the whole backup is read.

Note

Individual files can be restored from a database backup; however, locating and restoring a file takes longer from a database backup than from a file backup.

Additional Considerations for the Simple Recovery Model

Under the simple recovery model, read/write files must all be backed up together. This makes sure that the database can be restored to a consistent point in time. Instead of individually specifying each read/write file or filegroup, use the READ_WRITE_FILEGROUPS option. This option backs up all the read/write filegroups in the database. A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup. For more information, see Partial Backups.

Additional Considerations for the Full Recovery Model

Under the full recovery model, you must back up the transaction log, regardless of the rest of your backup strategy. A complete set of full file backups, together with enough log backups to span all the file backups from the start of the first file backup, is the equivalent of a full database backup.

Restoring a database using just file and log backups can be complex. Therefore, if it is possible, it is a best practice to perform a full database backup and start the log backups before the first file backup. The following illustration shows a strategy in which a full database backup is taken (at time t1) soon after the database is created (at time t0). This first database backup enables transaction log backups to start. Transaction log backups are scheduled to occur at set intervals. File backups occur at whatever interval best meets the business requirements for the database. This illustration shows each of the four filegroups being backed up one at a time. The order in which they are backed up (A, C, B, A) reflects the business requirements of the database.

Strategy combining database, file, and log backups

Under the full recovery model, you must roll forward the transaction log when restoring a read/write file backup to make sure that the file is consistent with the rest of the database. To avoid rolling forward a lot of transaction log backups, consider using differential file backups. For more information, see Differential File Backups.

Creating File or Filegroup Backups

The required BACKUP syntax for creating a file backup is:

BACKUP DATABASE database_name <file_or_filegroup> [ ,...n ] TO <backup_device>

Note

The first backup of a file or filegroup must back up the full file or filegroup. After you have created a full backup for a file or filegroup, you can create a series of one or more differential file backups that are based on that full backup. For more information, see Differential File Backups.

To back up files and filegroups

Note

File backups are not supported by the Maintenance Plan Wizard.

Using Differential File Backups

A file backup can serve as the differential base for differential file backups. This lets you supplement a file backup with a short series differential file backups. For more information, see Using Differential Backups and Differential File Backups.