asked
cleaning up T Log backups at the end of a full backup
Hello Experts , our full backup starts at 12.02 and completes at 4.00am .our full backup have all data in it and also getting t-log backup files same time .but the transaction log doesn't get truncated by backups .we can delete all t-log files from 12.02am -11.30 pm .or 11 pm . for cleaning up T Log backups at the end of a full backup, what would the TSQL look like? My full backup job query is : DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak'; BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10 DECLARE @backupSetId AS INT SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id = ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' ); IF @backupSetId IS NULL BEGIN RAISERROR( N'Verify failed. Backup information for database ''MYDB'' not found.', 16, 1 ) END RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId how do i add cleaning up T-log backups at the end of a full backup ? we are getting t-log backups every 24 minutes and getting full backup at night .
Are you deleting the transaction log backups for the previous day after the most recent full? If so, I'd be cautious in doing that as if you needed to restore to a point in time you won't be able to. Most shops keep things around for a period of time (a week, month, etc) before getting rid of them. I would suggest looking at Ola Hallengren's solution to help facilitate things. No need to re-invent the wheel when someone already has a tried and true method to do it for you. Ola's stuff is rock solid and many organizations use it for managing backups and history retention.
https://ola.hallengren.com/sql-server-backup.html Hope that helps! Hope that helps!
18 People are following this question.
