September 10, 2001 at 11:55 am
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.
Thanks,
Brian
September 10, 2001 at 3:54 pm
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!
Andy
September 10, 2001 at 7:33 pm
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... (ckempste@iinet.net.au)
SELECT @cmdline = @p_cleanuppath + '\dtdelete.exe ' + @p_dest2 + '*.bak' + ' ' +cast(@p_retaindays as varchar)
EXEC @v_error = master..xp_cmdshell @cmdline, NO_OUTPUT
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 11, 2001 at 7:33 am
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 http://msdn.microsoft.com
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
September 12, 2001 at 10:45 am
I am working on an article, but here is my ActiveX script that I use:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=74
Steve Jones
September 12, 2001 at 5:50 pm
Not bad Steve - have you considered just putting the VBScript into a job rather than as a DTS task?
Andy
September 13, 2001 at 9:22 am
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