Deleting backup files

[sakusan]

Hi,

Is there a way to automate the deletion of SQL back up files using maintenance plans?
I used the MP wizard and seemed to select to delete after 2 weeks, but I have 4 weeks worth of back up files in the location and have to delete manually?

Any advice appreciated.

 

[JayKusch]

That is one of the issues with MPs - lol.

Most seasoned DBAs forego using MPs and create their own scripts and jobs to handle backups, reindexing and such.

That being said ... no way to implement it into an MP.

You could create a sched SQL Job or NT task that calls a batch job that leverages a handy little tool called ForFiles which will help in purging needed files.

Here is an example of using ForFiles.

REM
REM  Purge Transaction Log Backups older than 2 days
REM
E:\DBA_Tools\ForFiles\FORFILES -pG:\MSSQL\MSSQL\Backups -s -m*.TRN -d-2  -c"CMD /C del @FILE"
REM
REM  Purge Database Backups older than 2 days
REM
E:\DBA_Tools\ForFiles\FORFILES -pG:\MSSQL\MSSQL\Backups -s -m*.BAK -d-2  -c"CMD /C del @FILE"
REM
REM  Purge Log Files older than 4 days
REM
E:\DBA_Tools\ForFiles\FORFILES -pG:\MSSQL\MSSQL\Backups -s -m*.Log -d-4  -c"CMD /C del @FILE"
REM
REM  Purge Text Files older than 4 days
REM
E:\DBA_Tools\ForFiles\FORFILES -pG:\MSSQL\MSSQL\Backups -s -m*.Txt -d-4  -c"CMD /C del @FILE"

Thanks

J. Kusch

 

[LawnBoy]

My SQL2K MPs delete the old files as specified, no problems even when the files are on remote servers.

Make sure the account the SQL Agent is running under has delete permissions on that folder.

--
The stagehand's axiom: "Never lift what you can drag, never drag what you can roll, never roll what you can leave.

 

[mrdenny]

Are you using SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[drtree]

Denny has a point in his question - things have changed in SQL 2005. To clean up old back up files, you'll need to set up a Maintenance Clean Up Task as part of your packages. Check out this link...

http://technet.microsoft.com/en-us/library/ms345177(SQL.100).aspx

Dr Tree

 

[sakusan]

Many thanks for the responses, I am running SQL 2005. In the wizard I did set up several tasks around DB maintenance and clean up tasks. I will have another look and give details to the exact MP I created, I will also give all the above a try and update.
Regards,