Solved

Query regarding backup maintenance plans

Posted on 2013-01-03
3
367 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
3 Comments
 
LVL 9

Expert Comment

by:MattSQL
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 500 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

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 20
SQL Script to find duplicates 16 20
Convert int to military time 8 20
SQL Server 2012 Express to Full 5 16
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

25 Experts available now in Live!

Get 1:1 Help Now