Backup Types and Options

Posted on 2011-04-29
Last Modified: 2012-05-11
Hi Everyone,

Bear with me since I am new to SQL Server. I have previously worked with Oracle.

I was taking some backups of different databases on my server. These include the System Databases, Master, MSDB, Model and so on.

I was wondering, why do some user databases only give me the option to do Full and Differential Backups, while others, allow me to do Full, Differential and Transaction Log backups. Can anyone explain why this is?

I thought at first it might be the Recovery Option for the paticular databases, but it turns out that some of the user databases that have their recovery option set to simple, will allow any type of backup, whereas others only allow the Full and Differential options. Can anyone explain how these options are determined.

Thank You,  
Question by:jonsuns7
    LVL 29

    Accepted Solution

    A database won't give the option for a transaction log backup if its recovery mode is set to Simple.
    You can check this setting by right clicking the database and selecting properties and looking at the options settings.
    LVL 29

    Expert Comment

    by:Paul Jackson
    LVL 29

    Expert Comment

    by:Paul Jackson
    LVL 29

    Expert Comment

    by:Paul Jackson
    The reason you are getting the option to backup a database that is set to simple recovery mode is because even in simple mode the database will still use a transaction log in case a transaction rollback is required but the transaction log gets truncated when a checkpoint occurs assuming there are no uncomitted transactions at the time of the checkpoint.

    A checkpoint will occur :
    (from sqlbooksonline)

    If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
    The log becomes 70 percent full.

    The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

    Long-Running Transactions
    The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:

    If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.

    The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint. Replication Transactions
    The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.

    See here for deeper explanation :

    Author Closing Comment

    Great Thanks for the help

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Need help with a query 7 53
    SQL HELP 2 65
    Help with SQL joins 9 32
    SQL Trigger or Function that updates table with old values 5 30
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    728 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

    15 Experts available now in Live!

    Get 1:1 Help Now