Transaction Log Backup Advice

Question

Transaction Log Backup Advice

laker_42

SSCarpal Tunnel

Points: 4785
More actions
June 1, 2006 at 9:00 am

#117915

We have been doing transaction log backups every hour on our Solomon server with a full backup every night. I use the following syntax for the transaction log backup:
Backup log db.log with init
I was changing the location of the backup files today & something hit me. I am using the same name for every log backup. Doing this, it overwrites the previous log backup. Which means that I would only be able to use the latest log backup to restore from, correct? How do most people handle transaction log backups? Do you use a maintenance plan? I thought about concantenating a timestamp onto the log backup name but then I would have to come up with a way to clear out the log files after the full backup. What am I missing here?
Thanks for any help you can offer!
John

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi John,

By far the easiest way is to create a maintenance plan and get that to clear out log files older than a certain amount. Typically, 48 hours worth of log files is sufficient as that will mean you're covered in case the full backup fails overnight. This is very much dependent on your needs for recoverability and your specific environment.

If you don't have weekend support for your SQL Servers you'd need to retain for slightly longer to cover for the possibility of a full backup failing on Friday night (and subsequently throughout the weekend) - meaning that your last good full backup was on Thursday. So you'd need all of Friday, Saturday, Sunday and part of Monday's logs if you needed to recover to say Friday.

I have used custom scripts before that concatenate the timestamp onto the backup name and then use another script that looks through the directory of log files using xp_diretree and xp_getfiledetails to determine which files to delete.

But like I said, the easiest solution is to use the maintenance plans.

Hope that helps,

Karl
source control for SQL Server

tosscrosby Hall of Fame Points: 3140 More actions · Answer 2

Or, within your job, do something like:

IF DATEPART (HOUR, GETDATE()) = 5

EXEC master.dbo.xp_backup_log

@database = 'JDE_PRODUCTION'

, @filename = '\\coldstandby\dumpsAllOther\jdedumps\wdent2\logs\tranFRI_LITE.bak'

, @init = 1

ELSE

EXEC master.dbo.xp_backup_log

@database = 'JDE_PRODUCTION'

, @filename = '\\coldstandby\dumpsAllOther\jdedumps\wdent2\logs\tranFRI_LITE.bak'

, @init = 0

Our backup initializes the file at 5:00 a.m and then appends for the reminder of the run. We use LiteSpeed but the IF->Else should work for you.

Terry

SQLBill SSC Guru Points: 51440 More actions · Answer 3

I do a full backup every day, a diff 12 hours later and tlogs every 4 hours.

Since you use WITH INIT, your tlog backups are worthless. You need ALL the tlogs since the last FULL backup to do a restore. Your's are missing.

I solve the problem of lots of tlogs, by having two jobs. The first job runs once a day and backs up the tlog using WITH INIT. The second job runs every four hours after that job and uses WITH NOINIT. That way I always have one full day of tlog backups (all I need since I do the full backup daily).

(BTW-all of my backups are copied to tape daily).

-SQLBill

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 4

Thanks for all the responses guys. You all confirmed what I just discovered. I ended up going with a process similar to what SQLBill suggested.

I am not sure what I was thinking when I set this up initially with the init. Thanksfully the Lord protected us from having to use the transaction logs to recover from. I would have been in big trouble with only the latest transaction log.

Thanks again!

John