Delete old transaction logs

dlukacdlukac Posts: 2
edited June 27, 2011 8:56PM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.