Backup Strategy for a multi-Terabyte database with multiple filegroups and mirroring

Posted on 2011-10-20
Last Modified: 2012-05-12
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!
Question by:CyprexxDev
    LVL 28

    Accepted Solution

    COPY_ONLY could definitely be an option for your read-only backups - it doesn't appear to impact the backup chain at all, but would allow you to restore a single filegroup in case of a disk failure. Especially since your filegroups can't be changed after the backup is taken (if they're set to read only), you should be safe with a COPY_ONLY backup.

    Another option is more frequent backups of your database log file. Each filegroup backup will include the transaction log because SQL Server wants to make sure it can roll forward or backward any transactions that aren't fully committed to the database yet - since some of those transactions could apply to the filegroup you're backing up (even though you, as the DBA, know they don't - SQL can't be sure), SQL Server backs up the entire log with the filegroup backup.

    However, if you set your transaction log to back up by itself more frequently (not just along with your full backup), you can keep the size small and manageable, and then a copy included with your individual filegroup backups wouldn't be as big a problem. Since you're already backing up filegroups that are in the range of 80GB each, a few GB of log file (whatever has happened since your last log backup) shouldn't impact your storage needs too greatly.

    Last, make sure your backup plan includes testing what you're taking! I can't count the number of times I've seen a backup plan that looks amazing on paper and is executed with unflappable precision every day for years, but then when it's needed, it turns out it's missing something critical and they didn't know it because it was never tested. Even though the storage needs are pretty intense, find a place on your network (or buy a place if you don't have one already - given your needs, a staging server hardly seems out of the question financially) where you can test the restore of your entire database on a regular basis. In addition to answering the question about whether you're including everything you need to make a good restore, you'll also answer the question about how long a from-scratch disaster-recovery would take.
    LVL 1

    Author Closing Comment

    I appreciate the response.  This helps greatly.

    Yes, financially, we have the ability to create a test system for restore purposes.  What I'll likely do it restore the Primary and read/write filegroups and try to restore a couple of the read only backups every now and then.  If I'm able to then use the system with the photos I've restored, I'll know it's working correctly.

    We do backup transaction logs frequently (once every 10 minutes) but currently we are in a transition stage going from a single filegroup system to the one I've explained.  Due to this, our transaction logs are huge since we are converting the system and have 100s of GB a day being transferred causing huge Transaction Log growth.  If I wasn't mirrored, I'd set it to simple right now to eliminate the need of truncating the log (which we do by backing up the log to 'NUL;').

    But this really helps - especially the answer about the Copy_only use.  I think that solves the problems we faced.  Thanks for your help!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now