?
Solved

Query regarding backup maintenance plans

Posted on 2013-01-03
3
Medium Priority
?
375 Views
Last Modified: 2016-02-11
I am in the process of creating backup maintenance plans my databases. In the data flow task, I’ve placed a ‘Maintenance Cleanup Task’ that follows ‘Backup Database Task’ .  In the ‘File age’ section, I only see an option of deleting files older than ‘n’ number of hours, days, weeks, months or years. Is there a way to delete files older than ‘n’ number of files?
Any suggestions are appreciated.
Thanks!
0
Comment
Question by:UConn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 38741969
No there isn't.

The assumption here is that because backups are being taken on a weekly/daily/hourly... basis then they should be cleaned up as such too. This logic falls over slightly if you take adhoc or out of band backups by manually running the jobs (which is often what you do when you set up the plans).

As a general rule - if you take daily backups and want to keep only 7 files then clean up files older than one week....
0
 
LVL 7

Accepted Solution

by:
armchang earned 1500 total points
ID: 38742043
Theoretically, there is a way but I'm thinking it is a pretty daunting task to do. It is only possible by using SQL Server Integration Services (SSIS).

This is my theory: If we can get the backup files in one folder location and loop through it, we can do some sorting on it's creation date and determine what is the oldest date (D1). We could then use that data(D1) to delete files (D2) that are older than the date (D1).

To get a start there is this forum discussing about the script for deleting files from SSIS:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/449edab3-ec20-4e37-a257-5e4dfce878df/

A
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38743635
Using Filesystem task, you can count the files with some predefined name and delete based on that.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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