December 1, 2004 at 5:37 am
Just inherited a server that performs a full db backup every midnight and immediately after that a transaction log backup. I don't know why this is necessary as there are no users logged on at this time, they've all gone home at 5.00pm. Is there a good reason for this?
Anyway, I now want to do an additional transaction log backup lunchtime everyday but do not want to change the original midnight backup job.
I am think of creating a new backup device to contain the lunchtime log backup. Is it reasonable to then have two different backup devices as long as I know the order to restore in the eventuality.
I have read the BOL and I think I'm right, but being a newbie any confidence helps. Regards
December 1, 2004 at 6:29 am
From my understanding complicates this things. If you backup up the log after a full or differential backup (rather than before), you need to restore this log additionally when you need to restore the db.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 1, 2004 at 6:43 am
Frank, That's exactly what I thought. I think I should delete the log backup job immediately after the full backup is done. Anyone disagree (he! he!)
December 1, 2004 at 7:34 am
It's my opinion you don't need that immediate transaction log and can turn it off.
Most likely, the person who set it up did it as a 'fail safe'. A lot of DBA's think that a Full Backup only backs up the state of the database AS OF WHEN THE BACKUP STARTS. That's not true. But if it was, then you would need to do a Transaction Log backup to get any transactions made after the Full Backup started. So I believe the person who set it up wasn't confident that the Full Backup actually backed up everything.
By the way you can do more Transaction Log backups and have them go to separate files, just as long as you remember the order to restore them.
-SQLBill
December 1, 2004 at 10:29 am
I suppose its possible they created this job in order to protect against a failed backup. Perhaps if a full backup failed, their log would fill up before the next nights full backup would kick off. In this scenario, even if a full backup failed their tlog backup would truncate the log and they would still have a daily recovery point.
Still seems pretty redundant to me though ;}
December 1, 2004 at 10:39 am
Thanx all. I feel confident now. Regards
December 1, 2004 at 10:57 am
Another possibility is that they realized that the full backup wasn't truncating the log, and added the log backup to achieve that, without considering recoverability at all.
Steve
December 2, 2004 at 7:24 am
This is a good reason why you'd want to archive your tran log before you perform the database backup.
You really don't need to have the next archive log file you run have the same transactions as what's in your database backup file - it's redundant (wasting space, and where I am, we're always looking for space).
December 2, 2004 at 11:21 pm
Exactly. The typical sequence is backup the transaction log immediately before doing a full backup. This transaction log backup will rarely be needed in a restore situation because of its proximity to the next full backup.
And, as bbdpres indicated, it removes the committed transactions from the log making it smaller and, therefore, making the full backup smaller (and faster).
- Jay
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply