A filegroup backup copies one or more SQL Server database filegroups instead of the entire database. It can avoid an impossible backup window, but it adds restore complexity and may weaken the recovery point objective (RPO). section 12.5
Mistake: choosing filegroups too early
Suppose a nightly ETL process overlaps the backup window. A weekly full backup plus daily differentials may be preferable. If differentials also become too large, back up different filegroups on different days—but accept that piecing together the restore is complex and an RPO of 24 hours may no longer be achievable. section 12.5
Use this order of preference:
- Move backups outside the ETL window when scheduling allows. section 12.5
- Otherwise consider weekly full backups with nightly differential backups. section 12.5
- Treat filegroup backups as a last resort and obtain explicit business acceptance of the RPO risk. section 12.5
Figure 12.6 Filegroup backup strategy
Use the strategy diagram to visualize distributing filegroup backups across different days. section 12.5
