Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: Transaction Log Backups

    I have to preface this with the fact that I am a DBA in training and still a novice - kind of an accidental DBA so to speak, but trying to learn. I need some clarification about transaction logs. I currently have created a maintenance plan with a full back up once a day and hourly transaction log backups. Since the transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up, if you had to restore the database would the last transaction log backup be sufficient for restoring all transactions performed since the last full backup? I guess I'm trying to understand what a transaction log backup contains. It seems to me if each time a transaction log is backed up there would be data missing because your performing a full database backup only once a day and each transaction log backup only contains data since the last log backup. When restoring would you have to restore all the transaction log backups to restore all data or just the last one? Does that make any sense? Again sorry - I'm still learning!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You will have to restore the full backup, then recover in order all available logs since that backup.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    19
    Here's a way to visualize transaction logs and backups. Oversimplifying, but it may help.

    Imagine you're using a graphics program to draw a person. You have everything done except the face (that's what's in the full backup). You add one eye, which is a transaction, and back it up. You add the nose, back that up, and so on. Now, if you lose that image, you have to restore the first main part, then each new part in turn. If you skip any of those parts, your picture won't be complete.

    Something else to remember: as you restore the main backup and each log (up to the last one), be sure to use the NORECOVERY option. When you restore the last (newest) log, use the RECOVERY option. You don't want your data to go back online until it's complete.

  4. #4
    Join Date
    Feb 2010
    Posts
    6
    Currently I have all the backups saving to the local server. I would like to move a copy of them to another server. In my reading it seems that I have to write a job to be able to do this as the Maintenance Plan wizard does not allow you to select a networked drive. Can you point me in the direction of writing a job to do this? I understand the basics of Transact-SQL so I don't know if it is necessary I write a script to do this or if a job can be created to do this. If I do have the files move to another server, I would have to write the script to move the full back up and then each transaction file as it is created?

    Thanks for your analogy by the way - it helped!

    I'm sure I'll be posting a lot of very basic questions here! Hope people are patient with my learning curve!

  5. #5
    Join Date
    Feb 2010
    Posts
    19
    If I understand your question correctly, you want to make two copies of your data every time you back up. You should be able to define your media set with a mirror, that is, a second destination. It doesn't have to be mechanical media, such as tape drives--it can be a file folder, as you're doing with the primary backup. Simply define your mirrored backup with the proper path. If the destination drive is accessible from your server and you have the proper permissions, the files should go there without any more effort on your part. As a bonus, that mirrored folder is recognized as a backup device, and you can restore from it just by identifying it in the restore command. As another bonus, you get a transaction log backup that is not on your server, which keeps your data safer.

    Here's another analogy for transaction logs. Suppose the main backup is your last bank statement and the transaction logs are the transactions that post to your account every day. The goal is to have the correct balance on your account. If you don't restore the log for a particular day, your balance won't reflect that day's business, so your balance will be off. Now imagine that you're dealing with thousands of accounts instead of one, and with hourly backups instead of daily.

    The whole point of transaction logs is to speed up backups. Instead of making a complete backup every hour, you only back up the changes you've made since the last backup. Restores take longer, but you don't do them as often.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Shiftmore
    The whole point of transaction logs is to speed up backups. Instead of making a complete backup every hour, you only back up the changes you've made since the last backup. Restores take longer, but you don't do them as often.
    Actually, the point of transaction logs is to give you the ability to do a point in time restore. If you do a full backup every hour on the hour, you can only restore the database as of one of those backups. If you have transaction logs, you can apply those and stop at a point in time. Like just before someone deleted the accounts table.

    A better analogy for a transaction log is a set of driving instructions. You can follow the directions (drive 1 mile, turn left, drive three miles turn right, etc..), but if you do not have a full backup as a starting point, the directions do absolutely no good. Also, with the instructions, you can reconstruct where you were at any point in your journey.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    If you don't want to copy the files, you can restore via a UNC path. I do it all the time for our staging server restores. I scripted everything out those using DOS scripts and ActiveBatch, works like a charm.

  8. #8
    Join Date
    Feb 2010
    Posts
    19
    Quote Originally Posted by MCrowley View Post
    Actually, the point of transaction logs is to give you the ability to do a point in time restore.
    Okay, that's a good reason. Another one, and this is very important, is to protect your data. If you make a backup every five minutes, you won't lose more than five minutes' worth. Speed is not "the whole point," as I said earlier. Sorry, it was late in the day.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quite alright.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •