Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Maintenance Plans - Delete old backup files Expand / Collapse
Author
Message
Posted Monday, November 15, 2010 9:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Post #1020855
Posted Monday, November 29, 2010 6:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:53 PM
Points: 1,030, Visits: 2,791
Hi Steve

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

Geth


Gethyn Ellis

gethynellis.com
Post #1027295
Posted Monday, November 29, 2010 10:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
It's yours.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1027422
Posted Wednesday, December 01, 2010 1:34 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:58 AM
Points: 3,924, Visits: 1,588
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.
Post #1028920
Posted Monday, April 11, 2011 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 2:15 PM
Points: 14, 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.
Post #1091646
Posted Tuesday, April 12, 2011 1:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
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.


If your server version is 2005 SP2, it has this issue.
Post #1092376
Posted Tuesday, April 12, 2011 1:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 2:15 PM
Points: 14, 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!!!!!
Post #1092394
Posted Tuesday, April 12, 2011 5:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1092505
Posted Wednesday, April 13, 2011 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 2:15 PM
Points: 14, Visits: 48
Sorry...

SQL Server 2008

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



10.0.2531.0 SP1 Standard Edition

Same version number as I gave you before.
Post #1093045
Posted Wednesday, April 13, 2011 11:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 7,053, Visits: 6,214
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1093050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse