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.
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?
16 People are following this question.
