Post reply

Full Backup after changing Changing recovery models

  • October 9, 2012 at 4:42 am

    #393340

    Hi Experts,

    I know we have to run a full backup if we change the recovery model from full--> simple-->full. My question is do we need to run full backup if changed from full--> bulk logged-->full?

    TIA

  • October 9, 2012 at 4:54 am

    #1547147

    No you don't, but you should run a log backup again, to re-gain point-in-time recovery.

    http://msdn.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

    Steve Hall
    Linkedin
    Blog Site

  • October 9, 2012 at 5:05 am

    #1547151

    Thanks Donor:-D

  • October 9, 2012 at 5:42 am

    #1547170

    Ratheesh.K.Nair (10/9/2012)

    Hi Experts,

    I know we have to run a full backup if we change the recovery model from full--> simple-->full.

    No, you can just run a differential to restart the log chain, no need for a full

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 9, 2012 at 6:00 am

    #1547175

    Thanks Perry

  • November 20, 2012 at 1:14 am

    #1560631

    Thanks Everyone.

    Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLE

    Also please let know whether its necessary to take these backups after changing recovery model for those changes to come in or is it as part of recovery??

  • November 20, 2012 at 1:55 am

    #1560643

    Ratheesh.K.Nair (11/20/2012)

    Thanks Everyone.

    Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLE

    My first question would be why you're changing the recovery model to bulk-logged for a short period.

    If you want to initialise the log chain so that you can take log backups, you need to take a full or diff backup after switching to bulk-logged, but the fact that you're switching back to simple suggests you don't want log backups, so I fail to see the point of changing to bulk logged.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2012 at 1:59 am

    #1560647

    Thanks Gail for the reply.

    We have a proc from developers to delete around 700 millions records from a table,this caused database log files to grow very high that the disk space reduced to 100GB from 1.9TB. The database is in SIMPLE recovery mode and i hope changing it BULK LOGGED RECOVERY model will help.

  • November 20, 2012 at 2:02 am

    #1560648

    No, it'll make it worse or no change at all.

    Simple recovery - minimal logging, log truncated on checkpoint

    Bulk-logged recovery - minimal logging, log truncated on log backup (if a full or diff backup has been taken since switching)

    So why would you switch to bulk-logged and have the log be harder to reuse with no other gain?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2012 at 2:05 am

    #1560650

    Thanks Gail.

    I was under the impression that only BULK LOG=minimal logging

  • November 20, 2012 at 2:09 am

    #1560651

    Nope.

    Simple and bulk-logged allow for minimal logging

    Bulk-logged and full allow for log backups

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2012 at 2:11 am

    #1560653

    Thanks a lot Gail.

    What i need to do to resolve this high space usage in this case?

  • November 20, 2012 at 2:15 am

    #1560655

    Delete in smaller chunks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2012 at 2:19 am

    #1560657

    In the procedure they are deleting values before a particular date.

    Values are deleted from a master table and inserting into a another table.

    Smaller chunks in the sense changing the date value to a shorter period will help??

  • November 20, 2012 at 2:32 am

    #1560661

    Maybe, but I meant deleting smaller chunks in a while loop.

    Have a search for batched deletes, should find you something

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply