Backup Strategy for a multi-Terabyte database with multiple filegroups and mirroring
Posted on 2011-10-20
This question is a little complex, but I'm hoping someone has some good answers to these questions:
We have a 4 TB database in Microsoft SQL Server 2008 R2 SP1 that holds pictures as a BLOB. We have split it into over 50 filegroups, each filegroup having 2.5 million photos. This is done automatically with horizontal partitioning. We simply add a new filegroup and split the partition when we are approaching the end of our current parition. Once a photo is in the database, it does not change after about a week so we set all but our most 2 recent filegroups to readonly .
What we want to do is create file group backups of each individual read only filegroup and ship them away. We then want to use the READ_WRITE_FILEGROUPS option on full backups to backup all read/write filegroups and the primary filegroup.
The problem I have is when I backup the read only filegroups individually, it seems to backup the transaction log of the entire database with each readonly filegroup. Since these filegroups have been reaonly for a long period of time, I'm not sure why the transaction log is getting backed up since no transactions from the log could possibly pertain to the filegroup backup. It's also increasing the size of our backups pretty substantially.
Should we possibly be backing up the read only file groups with Copy_Only?
Should we be concerned about all the read only backups being in the same backup set?
Is there any other specific option we should be using when backing up these file groups?
Is there anything outside the norm that I should know to utilize the above strategy?
The difficulty is that it's hard to test anything since each file group is so large.
Keep in mind, mirroring is turned on (ASYNC). I would love to go to the simply recovery model as we could do once a day backups on our read/write filegroups and a day of photos lost would be acceptable in an emergency situation, but mirroring forces us to stay with the Transaction log backups.
Any tips would be greatly appreciated!