How can I Incrementally number a transaction log backup filename?



  • I need to create transaction log backups of a database with the filenames somehow numbered incrementally, one per hour, throughout the day.

    Example:

    9am - mydb01.trn

    10am - mydb02.trn

    11am - mydb03.trn

    etc.

    I do not want the date or time appended to these because our SAN snapshots will take care of the versioning. These names have to be the same every day.

    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 basic T-SQL which is the limit of my T-SQL skills. I know I could just create a bunch of jobs with standard backup commands in them like below, with a different filename and scheduled time, however there has to be a better way.

    BACKUP LOG [mydb] TO DISK = N'X:\SQLBackups\mydb01.trn.trn'
        ,NAME = N'mydb_backup'
        ,COMPRESSION
        ,STATS = 10
        ,CHECKSUM
    GO
    


  • Backup Log permits you to use variables, you could use a variable for your disk name and assign it the appropriate name, for example:

    declare @diskname nvarchar(100)=N'X:\SQLBackups\' + N'MyDb' 
                      + Right(Concat(N'0', DatePart(hour, GetDate())),2) + N'.trn'
    

    print @diskname

    backup log [mydb] to disk = @diskname, ...etc


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2