SQL Server - Log Backups

logBackups.sql

DECLARE @db VARCHAR(128)
 
DECLARE c CURSOR FOR
SELECT [name] FROM sys.databases
 WHERE recovery_model_desc = 'FULL'
   AND [name] NOT IN ('master','msdb','model','tempdb','aspstate')
 
OPEN c
FETCH NEXT FROM c INTO @db
 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF sys.fn_hadr_is_primary_replica ( @db ) = 1
    BEGIN
        DECLARE @path VARCHAR(128) = 'K:\MSSQL\Backup\log\'
      
        DECLARE @today VARCHAR(9) = datename(w,sysdatetime())
      
        DECLARE @filename VARCHAR(128) = @path
             + @db
             + '_LOG_'
             + convert(varchar,sysdatetime(), 112)
             + '_'
             + substring(replace(convert(varchar,sysdatetime(), 114),':',''), 1, 6)
             + '.trn'
      
        DECLARE @sql NVARCHAR(512) = 'BACKUP LOG ' + @db + ' TO DISK = ''' + @filename + ''''
      
        PRINT @sql
        EXECUTE sp_executesql @sql
 
        DECLARE @retention DATETIME = DATEADD(d,-2,GETUTCDATE())
        EXEC xp_delete_file 0, @path, 'trn', @retention, 0
 
    END
 
    FETCH NEXT FROM c INTO @db
END
 
CLOSE c
DEALLOCATE c