Delete old transaction logs

dlukac

is there a way within sql backup to delete transaction logs that are older than 2 days from the file system? I'd prefer not to write a custom script to do this if this is a feature of backup 6.

petey

You can do this as part of a backup job, using the ERASEFILES option e.g.

EXEC master..sqlbackup '-sql "BACKUP LOG AdventureWorks TO DISK = [g:\backups\<AUTO>] WITH ERASEFILES = 2"'

Or you could use a standalone function to perform the deletion. The function name is sqbutility, and the parameters are as follows:

- function number, always 1032
- database name
- path to search for files, multiple files delimited by | e.g. path1\|path2\|path3
- backup type, D - full, I - differential, L - log
- retention period in days (default), or with h suffix for hours, or with b suffix for backup sets
- password. If none, provide an empty string i.e. ''
- no. of files deleted (output parameter)
- error text (output parameter)

E.g.

DECLARE @count INTEGER
DECLARE @error NVARCHAR(128)
EXEC master..sqbutility 1032, 'AdventureWorks', 'g:\backups', 'L', '2', 'password', @count OUTPUT, @error OUTPUT
SELECT @count, @error