question

Cuppadan avatar image
Cuppadan asked

How do I incrementally number a transaction log backup?

Hi folks,

I need to create transaction log backups that are numbered incrementally, one per hour, throughout the day: e.g

9am - dbtlog01.trn

10am - dbtlog02.trn

11am - dbtlog03.trn

etc.


I do not want the date or time appended to these because our SAN snapshots will take care of the versioning. I would like to do this without having to create a job for each hour of the day. I just haven't been able to figure this out using T-SQL. Any help on this would be greatly appreciated.

Thank you.

transaction-loglog-backupnaming
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered

Pseudocode, but it should give you the general idea. Adds the hour part of the current datetime to the file name.

declare @filename varchar(255)
select @filename = 'C:\BackupDirectory\DBName\DBName_TLog' + right('00' + convert(datepart(hh, getdate()), varchar(2))) + '.trn'
Backup log dbname to disk = @filename

If you want a full-on always-increasing sequence, then you'll probably have to do stuff like look in the msdb backup tables and find the most recent backup for that database, string slice it to get the number, add 1, and then build that into your file name. But... yuck.

Serious question, though - Why do you need it like this?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Thomas.

The question is moot now because there was a change of plans, however I'll still give you the credit for the answer.

Somehow my question was submitted a bunch of times even though I only summited it once.

1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.