File Backups
This topic is relevant only for databases that contain multiple filegroups. Under the simple recovery model, file backups are possible only for read-only filegroups; read-write filegroups can be backed up only with the primary filegroup.
Note
The IsReadOnly property is set on a filegroup, not individual files. If a filegroup is read-only (that is, if the IsReadOnly property is true for the filegroup), all of the files in the filegroup are read-only.
The files in a database can be backed up and restored individually. Using file backups 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 consists of several files located on different disks and one disk fails, only the file on the failed disk needs to be restored.
In general, specifying a filegroup during backup and restore operations is equivalent to listing each of the files contained in the filegroup. However, if any file of a filegroup is offline (for example, because it is being restored), the entire filegroup is offline.
Note
In SQL Server 7.0 and SQL Server 2000, file backups and differential file backups did not contain log records, and it was always necessary to explicitly apply a log backup to recover their data. In SQL Server 2005, however, all data and differential backups contain log records.
Creating File or Filegroup Backups
The first backup of a file or filegroup must back up the full database backup of the entire file or filegroup. For more information, see Full File Backups. Once you have created a full backup of a file or filegroup, you can create a series of one or more differential backups that are based on that full backup. For more information, see Differential File Backups.
Note
For information on file permissions that are set whenever a database is backed up or restored, see Securing Data and Log Files.
See Also
Concepts
Other Resources
Backing Up and Restoring Databases
File Backup and Restore Considerations for Related Features