locked
Backing up Database with Simple recovery mode RRS feed

  • Question

  • Hi All,
    I have shooted by a question which I dont have idea. can anyone help on below

    What are the actions we need to take on the Database which is in simple recovery mode when putting it in to backup rotation.

    Thanks


    Swapna

    Friday, August 22, 2014 4:19 PM

Answers

All replies

  • Hi Swapna,

    The actions required to take on simple recovery mode is depend what you need. On simple recovery mode the transaction log backups are not available. 

    Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

    • Log shipping
    • AlwaysOn or Database mirroring
    • Media recovery without data loss
    • Point-in-time restores

    You can go through the below msdn post for complete details

    http://msdn.microsoft.com/en-IN/library/ms189275.aspx

    If you are not satisfied with the answer, Please elaborate your question.


    Working as a Senior Database Analyst & Architect at Ministry of Higher Education in KSA

    • Proposed as answer by Haja Moinudeen Friday, August 22, 2014 4:37 PM
    • Unproposed as answer by Shanky_621MVP Monday, August 25, 2014 1:42 PM
    Friday, August 22, 2014 4:37 PM
  • Thanks for the reply Haja.

    I know these details and explained him the same but that was not his expectation. He wants the recovery mode as simple and want to setup backup..what change I need to be taken he asked again. I was blank and didnot understand what was his intention. Wondering is there some option like that.

    Thanks


    Swapna


    • Edited by SQL DBA1 Friday, August 22, 2014 5:24 PM
    Friday, August 22, 2014 5:23 PM
  • Hi Swapna,

    You can schedule the backup for him while the database was in simple recovery mode. i think no troubles from that point. Also from the user perspective no action is required, just we have to inform them hence we don't have a transnational backup recovering the database to certain time is not possible.

    If the database size is GBs restoring those will have some troubles, imagine you have database of 200 GB, if you have differential backup. You can restore the database on norecovery and a days before and restore the one day differential backup with recovery will minimize the restore time of such huge databases. You may explain such limitations.

    Hope i have clarified you, if not elaborate your trouble further

     


    Working as a Senior Database Analyst & Architect at Ministry of Higher Education in KSA

    • Proposed as answer by Haja Moinudeen Friday, August 22, 2014 5:51 PM
    • Unproposed as answer by Shanky_621MVP Monday, August 25, 2014 5:17 PM
    Friday, August 22, 2014 5:51 PM
  • You can use the Maintenance Plan Wizard:

    BOL: 'Use the Maintenance Plan Wizard


    This topic describes how to create a single server or multiserver maintenance plan using the Maintenance Plan Wizard in SQL Server 2014. The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.'


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Friday, August 22, 2014 7:24 PM
  • Hi Haja,

    Its not professionally correct to propose your own answer every time you post. Let other find your answer helpful and propose it its more appropriate way. You might propose your answer if you are 100 % sure it is correct and will answer OP query.

    Swapna,

    >>What are the actions we need to take on the Database which is in simple recovery mode when putting it in to backup rotation.

    I consider this as an unclear question. When scheduling a backup plan for DB which is in simple recovery you just first need to know RPO and RTO agreed and as per this you need to schedule backup. You can schedule both full and differential backup.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Monday, August 25, 2014 5:23 PM
  • I completely agree with Shanky. You need to understand and measure SLA then schedule the backup.
    Monday, August 25, 2014 5:59 PM
    Answerer