Reducing backup size and time by using read-only filegroups (SQL Server 2005 or later)

Many applications contain a substantial amount of data that does not change. For example, under time-based partitioning, historical data may be preserved indefinitely. Only a fraction of the database is subject to change at any given time. With SQL Server 2005 or later, static filegroups can be classified as read-only. NetBackup uses the filegroup read-only designation to optimize the total backup volume speed of recovery.

For backups, the advantage in using read-only filegroups is that you can reduce total media usage. You back up the read-only filegroups one time and retain the backup image indefinitely. This strategy reduces the total time you spend on backup operations because only read-write data is backed up periodically.

For recovery, the advantage is that you can bring your database on-line more quickly. Read-only filegroups do not need to be restored from backup media unless they are corrupted due to disk error or other hardware failure.

To reduce backup size and time by with read-only filegroups

Create a policy for read-only filegroups.
Create an Application Backup schedule with Retention set to infinite.
Create a policy for read-write filegroups.
Create an Automatic Backup schedule with the wanted Retention period and add the read-write filegroups batch file to the Backup Selections list.
Back up read-only filegroups.
In the NetBackup for SQL Server interface, select the policy you created in step 1 and perform this backup one time.
All read-only filegroups must be included in some combination of full, partial, or individual filegroup and file backups.

Note:

Immediately back up any filegroup when you change it from read-write to read-only.
Back up read-write filegroups.
This backup is done automatically through the schedule you created in step 3.
Or you can manually back up the read-write filegroups.
View the read-only backup set.

If necessary, confirm all read-only groups are backed up.