Post reply

Transaction log backup

  • January 25, 2006 at 12:45 pm

    #110710

    Please bear with me. Yet another involuntary DBA!!

    I'm using SQL Server 2000. I want to be able to restore to a point in time, losing an hour or less of transactions. So my plan is to do a full backup of master, and the application-specific database nightly, a differential of the application database each day at noon, and transaction log backups every hour. I use Enterprise Manager to schedule all backups, sending them to an external firewire hard drive, which is swapped out every night. My problem is that my transaction log backups must be renamed every hour, or I lose them...right? When I schedule the backup, my choices are to append or overwrite. How do I get to the end of the day with all my transaction logs on the hard drive, or can I? Thanks!!

  • January 25, 2006 at 12:56 pm

    #617016

    Hello Tony,

    In order for you to recover a database to a point in time, you need the following backup files:

    1. Weekly full backup file

    2. Last Differential backup file

    3. Transaction log backup files after the last differential backup

    You don't need to rename the transaction log backups. As you mentioned that you are taking a backup every 1 hour and by default, SQL Server adds the datetime part, if a maintenance plan is available.

    Hope you are clear now.

    Thanks and have a nice day!!!


    Lucky

  • January 25, 2006 at 12:57 pm

    #617018

    Probably the easiest ways for you to do this is to use the Maintence Plan Wizard.  Using this you will be able to set up a job that will backup up the transaction logs on your specified schedule as well as remove older files if you wish. 

    In addition, unless you are going for an increased speed in being able to recover after say a mid afternoon crash you probably do not need the incremental backup.  If you need to restore to a point in time you can simply restore your full backup and then apply all the required logs.

  • January 25, 2006 at 1:08 pm

    #617020

    You guys are fast!

    Here's my dilemma. Every hour when the tlog is backed up, my only choices are to overwrite or append. If I append, and have a whole days worth of tlogs in one backup, will that work for a restore? If I overwrite, and have just one tlog backup available at any one time, am I out of luck?

  • January 26, 2006 at 2:33 am

    #617092

    We are making a full backup every night, and transaction log backups every hour during night and every 15 minutes during day. Both file and log backups are stored for a week - not only the last day. I would recommend doing so, since you never know when you need to restore some older version into testDB to find out, how some error occurred etc.

    Differential backup at noon is IMHO not needed if you do the full backup every night and regular backups of log - it would unnecessarily complicate the restore process.

    When restoring, you then have to restore the last full backup and apply all log backups since the restored full backup. That means you must have all log backups from the last full backup as a minimum. If a single one log backup from that time is missing, you can not restore past this point.

  • January 26, 2006 at 6:14 am

    #617123

    I'm admittedly new to the backup functions of SQL Server, but as I understand it, it would be easier to restore, not more complicated, if you have the differential backup in many cases.  For instance, if I do a full backup at midnight and have to restore at 1pm the next day, I'd have to restore the full backup and then apply all of the t-logs (which could be quite a few if they're every 15 minutes).  If I added in the differential at noon, I restore the full, the differential, and then 4 t-logs, instead of about 20.

  • January 26, 2006 at 7:32 am

    #617161

    The restore process is quite well manageable in SQL Server, so that really none of the options is too complicated. I merely wanted to point out the fact, that it is not necessary to add a third level of complexity by creating differential backups if the full backup is every night. Yes the logs are quite a few if backup runs at midnight and you want to restore to 22:00, but I prefer to do it this way... it seems to me that it is easier to have one full backup and x log backups, than one full, one differential, and x/2 log backups. Also easier to store the backups and to make sure nothing gets lost or erased too soon. We tried the noon differential backup and decided not to use it. Maybe really just matter of personal preference 🙂

  • January 26, 2006 at 7:54 am

    #617169

    Append, it will work, they are numbered through 24 and restore without any difficulty. It may be a little annoying to go through each restore, just remember to return the database to an operational state on the last number.

    Kindest Regards,

    The art of doing mathematics consists in finding that special case which contains all the germs of generality.

  • January 26, 2006 at 7:56 am

    #617170

    Makes sense to me.  Like I said, I'm new to it - in fact, I just discovered how poorly our backup strategy is, so I've been reading like a madman about the best/recommended approach.  But it's been just that - reading.  It's good to hear the real world applications as compared to best practices/intentions.

    I think our strategy will be start out with the full,diff,trans approach and then we can always dial back as we discover what really works.

    Good info.

     

     

  • January 26, 2006 at 8:55 am

    #617207

    I really appreciate all the advice! I think I have a grasp on it now. The good thing is this is very small operation...3 employees, not a lot of transactions each day. I can play with it a little as we get going. It's just that the transactions are sooooo important, I'd rather have a little overkill, then go to restore and wish I had done more. Thanks!!

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

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