Backup's via Stored Procedure

  • I have a stored procedure that is sceduled in a job that will backup the databases and will also perform transaction backups. I want to know how to delete old .BAK files and .TRN files. It should be able to delete the files that are older than 2 weeks. Code with an explaination would be great.



  • Brian,

    There is no direct way in TSQL that I know of, you'd have to get the file name and either use xp_cmdshell or file system object to delete it. You could get the file names by reading the folder (file system object) or by reading from the backup history table. Reading the folder tends to be a better solution since if a db is dropped the bak files would sit there forever.

    Take a crack at it - if you can't get it to work, post what you've got so far and we'll see what we can do!


  • Hi

    Ive been meaning to post my backup script that does what you asked for. I use xp_cmdshell and do a call out to a small EXE file I use that deletes files older than N days. Email me in the time being and I will post the script and other OS files used onto this site when I get a chance... (

    SELECT @cmdline = @p_cleanuppath + '\dtdelete.exe ' + @p_dest2 + '*.bak' + ' ' +cast(@p_retaindays as varchar)

    EXEC @v_error = master..xp_cmdshell @cmdline, NO_OUTPUT



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • We use a VBScript job step to do our dirty work calling the File Scripting Object as Andy has mentioned. There should be some pretty basic examples at

    K. Brian Kelley

    K. Brian Kelley

  • I am working on an article, but here is my ActiveX script that I use:

    Steve Jones

  • Not bad Steve - have you considered just putting the VBScript into a job rather than as a DTS task?


  • I had, but I use the same job on multiple servers and use the Dynamic Variable Settings to get some information for the package. This way I can also do some error trapping in the DTS package. No reason it can't be script. I actually have a similar script on the backup server that receives my ftp files. It trims the folder to 3 backups for each db (plus logs).

    Steve Jones

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply