Sunday, July 31, 2011

Changing a Read-Only File Group to Read-Write with a Partial Backup Sequence

If you have a database using the simple recovery model with both read-write and read-only file groups, you may want to use differential partial backups.  This backup strategy makes sense (and saves backup disk space / backup time) Since you do not need to repeatedly backup data that is read-only.  This backup scenario is called a partial backup sequence.

For example, you may have:
FILEGROUP        TYPE
Primary          Read-Write
FG2              Read-only
FG3              Read-only
FG4              Read-write


After doing an initial full backup, you may want to backup the file groups enabled for write by performing a weekly (depending on your business needs for data recovery) partial full backup like this:
BACKUP DATABASE <database name> READ_WRITE_FILEGROUPS TO <backup device>

and a daily (also depending on your business needs for data recovery) differential partial backup like this:
BACKUP DATABASE <database name> READ_WRITE_FILEGROUPS TO <backup device> WITH DIFFERENTIAL

What if you change a filegroup from read-only to read-write?  What do you need to do to your backup jobs to account for the change?

A partial backup sequence must begin with a base partial backup before differential partial backups can be performed. Therefore, to include a filegroup that has been changed from read-only to read-write in subsequent partial backups, you need to create a new partial base backup. You create a partial base backup by specifying the READ_WRITE_FILEGROUPS in a FULL backup.  After that point, you have a base to do a recovery and include differentials as needed.

No comments:

Post a Comment