Post reply

backup for simple recovery db

  • October 30, 2012 at 3:33 pm

    #266290

    We have a database for network application Vcenter. it is setup to simple recovery mode, the database getting bigger and bigger, the transaction log backup is sometimes bigger than the differential backup and used up a lot of disk space.

    My question is do we need to do transaciton log backup if a database is setup to use simple recovery mode?

    Thanks,

    -- edit, I am wrong, it is using bulk-logged recovery mode, so it has the transaction log backup

  • October 30, 2012 at 3:52 pm

    #1554403

    You can't take a transaction log backup if the DB is in simple recovery model.

    DAtabase getting bigger is just a factor of more data in in.

    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

  • October 30, 2012 at 4:15 pm

    #1554412

    Thanks, when I change the database from bulk-logged mode to simple recovery mode, do I need to do a full backup right after that? By changing the recovery mode, does it break the backup log chain?

    Thanks

  • October 30, 2012 at 4:23 pm

    #1554413

    Putting the database into Simple Recovery mode breaks the log backup chain. Depending on what you are doing it for and assuming you are going to put it back to bulk-logged or full, you will want to take either a full backup or a differential backup (if you already have a full backup) after your process completes.

  • October 30, 2012 at 4:52 pm

    #1554417

    sqlfriends (10/30/2012)

    Thanks, when I change the database from bulk-logged mode to simple recovery mode, do I need to do a full backup right after that?

    No. If you switch back to full/bulk, you want to take a full or diff backup to restart the log chain.

    By changing the recovery mode, does it break the backup log chain?

    Yes.

    p.s. You're running that DB all the time in bulk-logged recovery? Are the risks of data loss in the case of a disaster acceptable?

    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

  • October 30, 2012 at 4:58 pm

    #1554421

    A little confusing,

    If I don't want to switch to full recovery mode, I just want to keep using simple recovery mode, do I need to do a full backup right after I changed from bulk-logged recovery mode to simple?

    Thanks

  • October 30, 2012 at 5:03 pm

    #1554423

    sqlfriends (10/30/2012)

    do I need to do a full backup right after I changed from bulk-logged recovery mode to simple

    No.

    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

  • October 30, 2012 at 5:07 pm

    #1554425

    Thanks, this is a database that instructor and vendor recommended to use simple recovery mode, we don't need to recover to point of time, but just recovered to previous night differetial backup is OK.

  • October 30, 2012 at 5:51 pm

    #1554437

    If the vCenter database that you refer to is a vmWare Virtual Center database, then there is no reason that you cannot run it in Simple Recovery mode. It is primarily a configuration and performance metrics collection database. Loss of data is not mission critical and should not have any impact on the hosts or guests, only on the ability to manage them through the vSphere client and on performance metrics collection. We run ours in Full Recovery anyway, but we also do transaction log backups every 5 minutes.

    You can refer to the following knowledge base article onthe vmWare site for confimation:

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1001046

Viewing 9 posts - 1 through 8 (of 8 total)

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