Created
March 16, 2018 13:13
-
-
Save richardbasile/c96c348c194e4d47f12cfb88c1e38837 to your computer and use it in GitHub Desktop.
SQL Server - Log Backups
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment