[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL 2005 DB Backup Doesn't Remove Old Backup Copies

Posted on 2012-09-14
Medium Priority
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
  • 2
  • 2
LVL 11

Accepted Solution

Simone B earned 2000 total points
ID: 38398861
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

ID: 38398862
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?

Author Comment

ID: 38398874
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

Author Closing Comment

ID: 38398898
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
ID: 38398906
You're welcome!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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