3

I've set up a maintenance plan to carry out:

  • a nightly full backup (overwrites file)
  • a 2 hourly transaction log backup (appends to 1 file separate to the full)

The database is in Full recovery mode.

The idea is to get the Tape backup agent to take a backup of the Full backup file each night after the SQL Server full backup finishes.

However I am confused as to how to set up the backup files for the transaction log.

What is the best practice for dealing with:

  1. Taking a regular trans log backup
    • should it create separate files for each one?
    • how would I get each backup off the server to tape at some point?
  2. Backing up the full db each night
    • should it overwrite the file each time
    • I've noticed that the backup file is quite large after 3 days of running even though it is set to overwrite. (DB=28Gb, nightly backup file = 58Gb) Why would this happen?

In summary, what are the best practices (in terms of file specifics) for setting up a Full and Trans Log backup plan and then backing those up to Tape?

Thanks, A-non-dba-developer

Improve this question

2 Answers 2

Reset to default
2

Personally I have a 12gb (and growing) database that has a nightly backup. I keep 5 days on disk each in their own file and copy them to tape nightly with a month or so retention. I also have transaction logs shipping every hour, each to their own file. I keep 3 days of these around, but never copy them to tape. If I ever upgrade the storage on the tape I might start this as well, though it's most likely not needed (IMO) as you have the nightly backups for in between.

As Peter said it's good to keep the backups on disk for at least a few days to make restores quicker and easier, not to mention restores into a secondary database for debug reasons easier. Also good to have a good length of retention for the tape. Not so much an issue for me, but good for if you have delete capabilities in your application and the client has an 'oops I deleted something... a month ago that I need today, can you get it back for me?' type moment.

Edit:
In answer to the comment and extenting my answer a bit here. I use a second/third SQL Job to manage the files for me. In SQL 2005 I had issues managing the files where it would not delete correctly so I wrote some code (VBScript) to do it for me on a scheduled task as far as deleting it. But in SQL 2008 either they fixed it, or I just did a better job configuring it and it deletes my old backups, old log shipments, and even another one to keep the DB healthy. I then use some VB script to copy my files to "tape" which I really should rewrite in C# the next time I get bored. I'm using the IOMEGA REV drive/disks as my "tape" and they are working out very well.

Improve this answer
3
  • Thanks for the info Tim. I suppose what I was trying to get at in my question was by what method do I manage the files created by the backup procedure? i.e deleting files so only 3 days worth are kept.
    – damitamit
    Jul 1, 2010 at 16:12
  • I updated accordingly. I thought about adding that before too, but skiped it for some reason.
    – Tim Meers
    Jul 1, 2010 at 18:06
  • Also, I'm saying job, because I'm at work and it's called a job here in SQL 2000. In 2008 it's a Maintenance Plan if I remember correctly.
    – Tim Meers
    Jul 1, 2010 at 19:04
1

I think the best practice is to keep perhaps three days' worth of full backups on disc, and get them swept to tape by the tape backup agent (veritas/tivoli etc).

Transaction log backups are often configured to go to the same drive area as the full backups, and the tape backup agent will capture them too.

This means that in the event of a restore requirement, you can restore very quickly from a backup from the last three days. Any further back, you'll need to get it off tape.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.