Backup and Recovery Best Practices › Backup Concepts › Backup Considerations › Backup Strategy Recommendations › Files and FileGroup Backups › How the Create Index Statement Impacts File and FileGroup Backups

How the Create Index Statement Impacts File and FileGroup Backups

The Backup statement requires that you back up entire FileGroups affected by a Create Index statement. This requirement exists in the following situations:

• If you create an index on a FileGroup, you must back up that entire FileGroup in a single backup operation. Microsoft SQL Server does not allow backups of individual files that are part of the affected FileGroup.
• If you create an index on a FileGroup separate from the FileGroup in which the table resides, then you must backup both FileGroups (the FileGroup containing the table and the FileGroup containing the newly created index) together.
• If you create more than one index on a FileGroup separate from the FileGroup in which the table resides, you must back up all the FileGroups immediately to accommodate these different FileGroups.

The Backup statement detects all of these FileGroup situations and communicates the minimum number of FileGroups that you must back up. Microsoft SQL Server reports this information when the backup job is run in one or more error messages, which will be written to the Activity Log by the Agent for Microsoft SQL Server.