Post reply

Transaction Log Management

  • November 18, 2013 at 2:42 am

    #285351

    Hi,

    I'm new to backing up and restoring databases, if you could shed some light for me on the correct way to

    1) Manage my transaction log

    2)Perform a restore

    I'd really appreciate it!

    My scenario is as follows - I create a nightly full backup, but would like to manage my transaction log as it is growing.

    - If I backup my transaction log, do I also then need to shrink it the log?

    - How often should I backup my transaction log, I'm thinking once a week.

    - Do I need to keep all the backups of the transaction log or just the latest backup?

    - If my last transaction log file backup is from 10/18/2013, my last full database backup is from 18/11/2013 and I now need to restore my database, do I need to include the backed up transaction log file in the restore process, and if so how do I do so?

  • November 18, 2013 at 2:54 am

    #1667056

    I recommend you search the internet for "Transaction Log Management". There is a very comprehensive series of articles by Gail Shaw. I'm not sure whether they're available on this site, her own site, or both.

    John

    Edit: this[/url] is a good place to start.

  • November 18, 2013 at 3:10 am

    #1667059

    This free e-book[/url] is really helpful for this topic.

  • November 18, 2013 at 3:16 am

    #1667061

    regProgrammer (11/18/2013)

    - If I backup my transaction log, do I also then need to shrink it the log?

    I'd look at managing the growth by backing up your log more frequently.

    How often should I backup my transaction log, I'm thinking once a week.

    It's really hard to say without knowing your environment, but this doesn't make all that much sense to me. You're doing a nightly full backup but only weekly log backups, which says to me you can afford to lose quite a bit of data and you don't plan to do any point-in-time restores, in which case why are you using Full recovery model?

    Do I need to keep all the backups of the transaction log or just the latest backup?

    The way you're doing things right now, there's not much point keeping those transaction log backups because your nightly full backups will obviate your weekly log backup most days anyway.

    If my last transaction log file backup is from 10/18/2013, my last full database backup is from 18/11/2013 and I now need to restore my database, do I need to include the backed up transaction log file in the restore process, and if so how do I do so?

    No, you would just need your full backup file from 18/11/2013. I can't think of a real-life scenario in which that transaction log backup from 10/18/2013 will be much use to you now.

  • November 18, 2013 at 3:43 am

    #1667067

    Thanks for the replies, I'm just looking at putting a plan in place for backups at the moment, your feedback really helped.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy