• SQL 2008 or Windows Script to Recursively Delete Old .bak Files on HDD
    3 replies, posted
Using SQL or Windows batch scripting, how would I delete old .bak files older than X days on a drive? I want to just specify (for example) D:\ and have this script search the entire drive for old shit.
If you're using transact SQL then you should be able to do [code]exec sp_configure go exec sp_configure 'xp_cmdshell', 1 go reconfigure go[/code] This should expose a CMD shell functionality to SSMS, E.G. [code]xp_cmdshell 'del D:\file.txt'[/code] I'm not sure of a way to ascertain file dates with the command shell however.
I want to do something more like del D:\*\*.bak. I want to specify the D: drive and have it look for/delete .bak files older than 90 days.
I believe to do a recursive delete you can do a 'del /s D:\*.bak' I was thinking, and it might be a pain in the ass to set up, that you have a system table set up that stores all the locations of the .bak files, has their date in it, and then does GETDATE() stuff to see if it's older than 90 days than does a [code]declare @period datetime = DATEADD(month, -3, GETDATE()); declare backupCursor CURSOR for select [bakLocation], [fileDate] from [sysBackupInfo] open backupCursor fetch next from backupCursor while @@FETCH_STATUS = 0 BEGIN if ([fileDate] <= @period) BEGIN xp_cmdshell 'del ' + [bakLocation] END END fetch next from backupCursor; close backupCursor deallocate backupCursor go[/code] Probably would be a bitch to set up but it could work. I should mention I have no idea how date arithmetic works and I can't guarantee that will work, but seeing as I know TSQL better than batch that's the best I can do for you. At least this way you can set it up to be a cron job
Sorry, you need to Log In to post a reply to this thread.