To back up files and filegroupsA file or filegroup backup copies one or more files of a specified database, allowing a database to be backed up in smaller units: at the file or filegroup level.
When creating file and filegroup backups, the transaction log is not captured by the backup operation. Therefore, transaction log backups must be created after a file or filegroup backup is created, otherwise any changes to the database files, backed up in the file or filegroup backup, would be lost if the file or filegroup backup is later restored.
For example, if a database is comprised of filegroups filegroup_a and filegroup_b, but there is only time during the week to back up half of the filegroups per evening, a backup procedure could be:
If any tables and indexes are created that span multiple filegroups, all filegroups affected by the creation of the index must be backed up together, followed by creating a transaction log backup, otherwise only parts of a table or index would be backed up, preventing the creation of the table or index from being recovered if later restored. Microsoft® SQL Server™ generates an error if any of the filegroups that the table and index spans are missing when a filegroup backup is created. For example, if a table is created on one filegroup, and a nonclustered index for the table is created on a separate filegroup, then both filegroups must be backed up as a single unit.
Therefore, when designing databases with tables and indexes arranged on multiple filegroups, the requirements to create filegroup backups involving these tables and indexes need to be considered.
To back up files and filegroups
To create a transaction log backup