SQL 2005 DB Backup Doesn't Remove Old Backup Copies

Posted on 2012-09-14
Last Modified: 2012-09-14
I used SQL Server Management Studio Maintenace Plan wizard to create a backup job that backs up a database nightly to a network location and is working as planned. My issue is that it creates a new .bak file with each backup (as I want it to) but after running for two weeks I have 14 .bak files. Is there an easy way to have the job also delete old .bak files that are older then 7 days?

I don't know any SQL scripting or coding so if there is a way to do this from the GUI that would be ideal.

Thanks in advance!
Question by:AIC-Admin
    LVL 11

    Accepted Solution

    In one of the first steps in the wizard you can select the tasks you want to configure. The one you're looking for is Maintenance cleanup.

    LVL 16

    Expert Comment

    The .bak backup files are just files - they're not referred to in any database or anything - so you can just go to that directory and delete whichever ones you want to get rid of, using Windows Explorer!

    You could add code into the backup maintenance plan (it's an SSIS package) to delete any files older than 7 days - is there someone in your organisation who writes SSIS packages?
    LVL 3

    Author Comment

    I am sure that I could ask our SQL programmer to do write a SSIS package but I was trying to just finish the task myself. I know I can delete them via explorer but the idea is automation so I don't have to manually delete the files or worry about forgetting to clean them up and eating up disk space.

    I will check the "Maintenance Cleanup Task" that buttercup1 suggested... I must have totally over looked that. Thanks
    LVL 3

    Author Closing Comment

    Since I already had the plan in place I just added a Subplan to run the Maintenance Cleanup Task and set it to delete files with extension bak from the network backup location that are older then 7 day(s). Thank you for the guidance!
    LVL 11

    Expert Comment

    by:Simone B
    You're welcome!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now