December 28, 2011 at 2:11 am
Hi all,
I need help 🙂
I have a structure in a directory that keeps the backups but I need to delete the backup files that are old enough say older that last 5 days.
I have come up to a point where I have created a table that contains the names of the files to be deleted. The problem is how to actually delete these files on that particular location.
I am not much aware about xp_cmdshell and how it can be useful here though i used this command to list all files and to truncate which need not be deleted.
So finally my table contains all the files that are not required and must be deleted.
Please help.
December 28, 2011 at 2:26 am
Have you considered using a maintenance plan to delete your old files? You can set it to remove all files older than a specified number of days.
John
December 28, 2011 at 2:31 am
No I m not using Maintainence Plan I have a generic SP that creates backup so I need another SP to delete the old files which are of no longer use.
Instead of manually deleting the files I need to automate the process.
December 28, 2011 at 2:52 am
But why make things difficult for yourself? This is what maintenance plans were designed for. Even if you don't use them for the backups themselves, you can still use them to get rid of old files.
John
December 28, 2011 at 3:00 am
Because thats the approach I have been asked to work,
Earlier we used Maintenance Plan but now we have our own script and to delete old backups also I need to create the script.
December 28, 2011 at 3:08 am
kritika (12/28/2011)
to delete old backups also I need to create the script.
No you don't. You need to keep using maintenance plans. Tell whoever has asked you to roll your own solution that to do so adds an unnecessary layer of complexity. If you must customise the backup solution, fine, but as you've learnt, it's just too difficult to do so for the cleanup as well. Good luck!
John
December 28, 2011 at 3:53 am
To be honest, I'm not a big fan of maintenance plan either and prefer using my own custom scripts. John might think it makes the process more complex, but I see it as more flexible.
To delete your old backup files you should use the xp_delete_file in the master database. Yes, it's an undocumented procedure, but you will find lot's of examples on the web.
Ola Hallengren also has a very good procedure for backups and removal of old files which you can use as a starting point.
[font="Verdana"]Markus Bohse[/font]
December 28, 2011 at 9:21 am
You could create the maintenance plan to delete the files (using the Maintenance Cleanup Task) then "script" it out so you can see the TSQL that is generated by it. You can then modify that to suit your needs? It's pretty straightforward, really...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 28, 2011 at 9:25 am
MyDoggieJessie (12/28/2011)
You could create the maintenance plan to delete the files (using the Maintenance Cleanup Task) then "script" it out so you can see the TSQL that is generated by it. You can then modify that to suit your needs? It's pretty straightforward, really...
The Best of Both the Worlds 🙂
December 30, 2011 at 3:17 am
thanks all this has been done.
I used xp_cmdshell 'del....' command
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply