none
Recovery Model RRS feed

  • Question

  • Hi All,

    We have databases with Full recovery model. But there were not much transactions with respect to that databases. Am new to DBA, my superior asked me to take backups everyday at end of day. Is it reecommended to change the recovery model  to simple.

    Regards,

    Venkat.

    Friday, February 22, 2013 7:32 PM

Answers

  • Please ask the same question to your supervisor because he is the best person to answer this.. No,I am not being rude but giving you the right answer...

    Recovery model defines how much of data you want to recover in case of data loss or disaster and this is the baseline to understand which recovery model to use. nobody knows on this more than you supervisor

     

    If your case can afford one day's data lost(in worst case scenerio), then switch it simple recovery mode and do full backup once a day.

    If your case can afford  3 hrs of data lost(in worst case scenerio) and the database small enough and has less no of transactions(as you said above)
     then switch it simple recovery mode and do full backup once a day and differential backups every 3 hrs. Again, It will be guessing game for anyone other than your supervisor to tell you this.

    Also, there will be SLA(Service Level Agreements),RTO and RPO objectives, you need to meet. All this goes into thinking before you make the switch. Thats why I said your supervisor can tell you on this.
    or post your RTO and RPO requirements, we can help.

    or in simple words, tell us how much data loss you can afford  in worst case scenario, may be we can help then...


    Hope it Helps!!






    Friday, February 22, 2013 7:43 PM
  • Usually for the Production server, the database full recovery model is an good option(as every one knows) how ever the proper backup strategy(how we want to recover the data )& knowing the business risk/Impact (the way we need to recover the data to point-in-time matters) is an important .

    For Development/test server ->keeping an database is simple that's fine but again it all depends on the logic of the business and their Impact/risk on the applications.

    so simply don't change the recovery model from full to simple,where if you did then your log backup chain will break & you lost the data to meet point-in-

    so please go through the below one to get clear understanding-

    Considerations for Switching from the Simple Recovery Model
    http://msdn.microsoft.com/en-us/library/ms178052%28v=sql.105%29.aspx


    Recovery Models (SQL Server)
    http://msdn.microsoft.com/en-in/library/ms189275.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, February 22, 2013 8:27 PM

All replies

  • Please ask the same question to your supervisor because he is the best person to answer this.. No,I am not being rude but giving you the right answer...

    Recovery model defines how much of data you want to recover in case of data loss or disaster and this is the baseline to understand which recovery model to use. nobody knows on this more than you supervisor

     

    If your case can afford one day's data lost(in worst case scenerio), then switch it simple recovery mode and do full backup once a day.

    If your case can afford  3 hrs of data lost(in worst case scenerio) and the database small enough and has less no of transactions(as you said above)
     then switch it simple recovery mode and do full backup once a day and differential backups every 3 hrs. Again, It will be guessing game for anyone other than your supervisor to tell you this.

    Also, there will be SLA(Service Level Agreements),RTO and RPO objectives, you need to meet. All this goes into thinking before you make the switch. Thats why I said your supervisor can tell you on this.
    or post your RTO and RPO requirements, we can help.

    or in simple words, tell us how much data loss you can afford  in worst case scenario, may be we can help then...


    Hope it Helps!!






    Friday, February 22, 2013 7:43 PM
  • Usually for the Production server, the database full recovery model is an good option(as every one knows) how ever the proper backup strategy(how we want to recover the data )& knowing the business risk/Impact (the way we need to recover the data to point-in-time matters) is an important .

    For Development/test server ->keeping an database is simple that's fine but again it all depends on the logic of the business and their Impact/risk on the applications.

    so simply don't change the recovery model from full to simple,where if you did then your log backup chain will break & you lost the data to meet point-in-

    so please go through the below one to get clear understanding-

    Considerations for Switching from the Simple Recovery Model
    http://msdn.microsoft.com/en-us/library/ms178052%28v=sql.105%29.aspx


    Recovery Models (SQL Server)
    http://msdn.microsoft.com/en-in/library/ms189275.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, February 22, 2013 8:27 PM