Microsoft SQL Backup Methodology

Last post 01-06-2011, 7:23 PM by Liam. 6 replies.
Sort Posts: Previous Next
  • Microsoft SQL Backup Methodology
    Posted: 11-04-2010, 10:19 PM


    I am curious as to the best SQL Backup Methodology.  I would appreciate everyone explaining the backup methodology they use.

    In addition, I have some questions:
    -- How should I separate out my sub clients?  I'm think the default can have the system databases and then create a subclient for FullRecovery DB's and one for SimpleRecovery DB's.  If that's sounds right, what methodology should I use to back those up?

    -- Should I recommend to my DB's that all DB's be set to FullRecovery.  Is there any benefit to every having a SimpleRecovery DB?

    -- How can I minimize the space on the backup?  I want to keep backups for a year and I realize this can be intense on data.  What is the best methology to backup these DB's daily and keep the data backed up to a minimum?

  • Re: Microsoft SQL Backup Methodology
    Posted: 11-08-2010, 12:23 PM

    Hi Justin,

    Every enviornment is different so determining what is the right configuration for your company needs would be tough. SQL supports Full, Transaction Log and Differential backups. Transaction Log backups are the only backup type in SQL that will perform Log Truncation. For databases in a Full Recovery Model, you are going to want to schedule Transaction Log backups in order to keep the size of the LDF file manageable.

    Full Recovery Model allows for Transaction Log backups to be taken. More frequent Transaction Log backups would give you better Point-in-Time recoverablity.

    Simple Recovery Model does not support Transaction Log backups because it manages its own log truncation.

    As far as subclient configuration, the default subclient is going to be the "Catch All" Subclient and will discover new databases at the time of backup as they are created on the server.

    The subclient configuration you mentioned isn't a bad strategy as it would allow you to schedule the System Database, Full Recovery Model Databases and Simple Recovery Model databases differently if desired although CommVault does have a built-in set of conversion rules that help with single subclient configurations.

    If you do not want CommVault to convert according to these rules, creating subclients as you mentioned would allow you to schedule different Recovery Models differently.

    Below is some additional information from Microsoft on SQL Server Backups.

    Backup Under the Full Recovery Model

    Transaction Log Backups


    Ron Potts
    CommVault Messaging Support
  • Re: Microsoft SQL Backup Methodology
    Posted: 11-17-2010, 4:20 PM

    I've tried the above method of unchecking the "Convert" option so that I can have simple and full recovery DBs in the same subclient and still run transaction log backups.  The only issue for me with this solution is that currently all the simple DBs that can't be backed up by a tlog backup all throw error messages in the event viewer.  This makes the event viewer very messy, especially since I'm testing running tlog backups every 15 minutes, so every 15 minutes I get 5 errors in my logs for simple DBs that failed to backup as a tlog.  Any way to get rid of these errors?



  • Re: Microsoft SQL Backup Methodology
    Posted: 11-17-2010, 4:33 PM

    Hi chadtandy,

    Sorry for the confusion. Simple Recovery Model Databases can not have Transaction Log backups ran against them. This is not supported by Microsoft. If you uncheck the convert rules on a subclient that contains Simple Recovery Model databases, we will issue the Transaction Log backup agasint it anyway and Microsoft will just return an error and those databases will Fail. If you are looking to configure Transaction Log backups every 15 minutes, your options would be to:

    1. Allow the Simple Recovery Databases to remain in the same subclient, convert to differential and run as a differential every 15 minutes. Full Recovery model databases in the same subclient will NOT convert and will run as Transaction Log Backups.

    2. Move the Simple Recovery Model Databases into their own subclient and schedule this on a different backup schedule.

    3. Find out if these database are required to be in a Simple Recovery Model and if not, change them to a Full Recovery model so they support Transaction Log backups.

    Let me know if this helps.


    Ron Potts
    CommVault Messaging Support
  • Re: Microsoft SQL Backup Methodology
    Posted: 11-17-2010, 5:07 PM

    Thanks, Ron, looks like I'll have to create an additional subclient.


  • Re: Microsoft SQL Backup Methodology
    Posted: 12-02-2010, 12:15 PM

    One other thing related to this, can I reserve a backup stream for my SQL tlog backup jobs?  I need to be able to guarantee that critical dbs will be backed up every 30 minutes and I can't guarantee that once all our nightly jobs fill up the queue.



  • Re: Microsoft SQL Backup Methodology
    Posted: 01-06-2011, 7:23 PM
    • Aplynx is not online. Last active: 09-18-2019, 11:53 AM Liam
    • Top 10 Contributor
    • Joined on 05-04-2010
    • New Jersey
    • Master
    • Points 1,706

    You should be able to up the priority/precedence of the specific jobs, clients or agents. that should interrupt any running jobs and allow the backups to run for the sql clients.


The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy