Auto Delete backups using Backup Maintenance Wizard

  • 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

  • Which database maintenance script? The maintenance plan wizard? The maintenance plan designer. There is a task (maintenance cleanup) to handle this.

  • 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

  • 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

  • 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.

  • 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.

  • 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