December 9, 2007 at 4:32 pm
I've installed our first SQL 05 server and using the Database Maintenance Script I seem to be unable to auto delete old backup files.
On SQL 2000 I had a "remove files older then" feature, on 05 I don't seem to have this option or am I missing something?
Thanks
December 9, 2007 at 8:37 pm
Which database maintenance script? The maintenance plan wizard? The maintenance plan designer. There is a task (maintenance cleanup) to handle this.
December 9, 2007 at 9:00 pm
Maintenance plan wizard yes.
Where do I find the cleanup task?
I don't want to cleanup history btw, which to my understanding is just activity logs? I want to remove backups older then x days.
Thx
December 10, 2007 at 7:05 am
Starting the Maintenance Plan Wizard >>
Give a name and Description >>
Give a single schedule, as I assume this will be a single task. >>
Choose "Clean Up History" maintenance Task >>
(Cleanup History task deletes historical data about Backup and Restore operations. This wizard allows you to specify the type and age of the data to be deleted.)
You can select any combination from the following in the "Define History Cleanup Task" window:
Backup and restore History
SQL Server Agent job history
Maintenance plan history
Remove historical data older than:
(default) 4 weeks
.. That should do it.
Happy T-SQLing!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 10, 2007 at 7:35 am
Once you save the maintenance plan, open it again by double clicking it. You will get a "toolbox" in the lower left.
The History Cleanup task deletes meta data in the tables and logs.
The Maintenance Cleanup Task will delete backups. Drag that to your plan, find the last task (follow the arrows) and make an arrow from that task to your new maintenance cleanup task. Double click the task to configure it to your needs.
December 10, 2007 at 8:08 pm
OK, I added a maintenance cleanup task to the bottom of the flowchart. One problem I think I may end up having is that I need to pick a directory and then delete based on extension which will be BAK.
But in the original backup it creates a dir for each backup, so I hope that the above will go into subdirs to delete these old backups.
December 11, 2007 at 7:41 am
If you have SP2, you can set this in the Maintenance plan cleanup.
Use .bak, the standard extension. SQL Server will find the backups.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply