SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plans - Delete old backup files


Maintenance Plans - Delete old backup files

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: Administrators
Points: 139528 Visits: 19413
A short discussion and screen shots to show how to delete old full backup files. Discuss what the values in the dialogs mean, show how this handles sub folders, talk about timing with this happening only after the next full works. Include notes about how to make this work and keep 2 or 3 full backups.

If you want to make this a series, I'd extend this to a couple more articles
- discuss differentials, how to remove old ones, how to keep 1 full a week, 2 most recent diffs, then remove old diffs with the next full
- remove old log backups. Issues with full backups, timing, can you keep 2 sets of logs (since last 2 fulls).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2647 Visits: 2905
Hi Steve

If you still need someone to write this article I will give it ago.

Geth

Gethyn Ellisgethynellis.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: Administrators
Points: 139528 Visits: 19413
It's yours.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5589 Visits: 1619
I am bad at writing articles but the easiest I think is to add a step next to backup command in job schedule. Add this command to next step and it will take care of deleting old files. All you have to change is the number you want to retrieve.
I am using this on all server across board on 361 servers for last two years and never had a problem.

Command:


DECLARE @currentdate datetime
DECLARE @olddate datetime

set @currentdate = CURRENT_TIMESTAMP
set @olddate = @currentdate - 1 -- Change number here depending how many days of backup file you want to retain on server.

-- Add below line for each database.

EXECUTE master.dbo.xp_delete_file 0,N'G:\<Backup file path>\<your database name>',N'bak',@olddate,1
EXECUTE master.dbo.xp_delete_file 0,N'G:\<Backup file path>\<your database name>',N'bak',@olddate,1



-- Backup file path example : G:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Backup\<your database name>


SQL DBA.
TheHose
TheHose
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 48
I have used the following, but it works only 80% of the time. Sometimes it will not delete the required files. And I don't know why, this bug has been around for sometime and I hope Microsoft comes up with a fix, or most DBAs will be asking for Oracle as a replacement;

Example:
- Begin code block

DECLARE @currentdate datetime
DECLARE @olddate datetime

set @currentdate = CURRENT_TIMESTAMP
set @olddate = @currentdate - 1 -- Change number here depending how many days of backup file you want to retain on server.

-- Add below line for each database and/or path.

EXECUTE master.dbo.xp_delete_file 0,N'E:\Backup\SQL_INDEX_REBUILD',N'bak',@olddate,1

- End code block

As I stated before this will work but it's not reliable..... I would like to thank all of you and this forum which has been the only real workaround I have seen in 4 months. If anybody can offer any other insight to this issue, please do. Cool
Wildcat
Wildcat
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1177 Visits: 1444
TheHose (4/11/2011)
I have used the following, but it works only 80% of the time. Sometimes it will not delete the required files. And I don't know why, this bug has been around for sometime and I hope Microsoft comes up with a fix, or most DBAs will be asking for Oracle as a replacement;

As I stated before this will work but it's not reliable..... I would like to thank all of you and this forum which has been the only real workaround I have seen in 4 months. If anybody can offer any other insight to this issue, please do. Cool


If your server version is 2005 SP2, it has this issue.
TheHose
TheHose
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 48
Version is SQL Server 2008

Microsoft SQL Server Management Studio 10.0.2531.0
Microsoft Analysis Services Client Tools 10.0.1600.22
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3615
Operating System 5.2.3790


Oh it's still an issue!!!!!
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18023 Visits: 10039
TheHose (4/12/2011)
Version is SQL Server 2008

Microsoft SQL Server Management Studio 10.0.2531.0
Microsoft Analysis Services Client Tools 10.0.1600.22
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3615
Operating System 5.2.3790


Oh it's still an issue!!!!!


That is your client - not necessarily the server you are connecting to. Verify that the server has been upgraded to SP2 with hot fix (9.0.3054 minimum version).

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

TheHose
TheHose
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 48
Sorry...

SQL Server 2008

via: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')



10.0.2531.0 SP1 Standard Edition

Crazy Same version number as I gave you before. w00t
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35685 Visits: 9261
Guys, can you take the technical discussion to another forum? This thread is an article request thread, not a technical "I have an issue" thread.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search