SQL Server Agent Question
Sql Server 2008: I have SQL Server Agent configured to run a Maintenance Plan every 12 hours. This plan backs up the databases to an External HDD plugged into a USB socket on the server. The Maintenance Plan is configured so that the Backup Set will expire after 14 days. When I set up this backup, I expected the backup files to be deleted after 14 days when the Backup Set expires. This does not happen. The files accumulate on the External HDD drive, and I have to manually delete them in order to prevent the disk filling up. Is there a way to automate the deletion of these expired backup files?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> I could incorporate your script into the maintenance plan and let that do the clean up for me.
yes, kindly try that and let us know whether it works or not. If so, then problem solved.
otherwise, we might need to use your current login to run the Maintenance plan or SQL job to delete the older files.
yes, kindly try that and let us know whether it works or not. If so, then problem solved.
otherwise, we might need to use your current login to run the Maintenance plan or SQL job to delete the older files.
ASKER
I think my current login is not relevant. Sql Server is started with the local system account and it is that which is used to delete the files. Yes, I am opening SSMS with my local account but SSMS is just a client which interacts with the SQL Server.
>> Yes, I am opening SSMS with my local account but SSMS is just a client which interacts with the SQL Server.
yes, that confirms that your Local system can access the files and delete it without any issues.
If there is anything that is making it not work then we need to identify that and fix it out..
yes, that confirms that your Local system can access the files and delete it without any issues.
If there is anything that is making it not work then we need to identify that and fix it out..
this is a batch script I use to delete files as needed recursively in all directories. ForFiles.exe is a built in windows function. Note use this at your own risk but it's worked for us without issue. I have SQL server agent call this job as the last step of the backup.
Just create a new step as an operating system type running as the sql server agent service account and point it at the bat file.
Just create a new step as an operating system type running as the sql server agent service account and point it at the bat file.
REM THIS IS A RECURSIVE SEARCH.
REM This will delete *.txt files found in E:\DataStorage and *.bak found in E:\DataStorage\SQLBACKUP\ that are 20 days or older.
forfiles.exe /p E:\DataStorage\ /s /m *.txt /d -20 /c "cmd /c del @file
forfiles.exe /p E:\DataStorage\SQLBACKUP\ /s /m *.bak /d -20 /c "cmd /c del @fileASKER
I incorporated your script into my maintenance plan. So I think I can close this question now. Thank you very much for your help.
ASKER
So have I originally understood correctly that the Maintenance Plan should delete expired backups?
I could incorporate your script into the maintenance plan and let that do the clean up for me.