November 15, 2010 at 9:06 am
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).
November 29, 2010 at 6:27 am
Hi Steve
If you still need someone to write this article I will give it ago.
Geth
Gethyn Elliswww.gethynellis.com
November 29, 2010 at 10:01 am
It's yours.
December 1, 2010 at 1:34 pm
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.
April 11, 2011 at 12:10 pm
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. 😎
April 12, 2011 at 1:07 pm
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.
April 12, 2011 at 1:27 pm
Version is SQL Server 2008
Microsoft SQL Server Management Studio10.0.2531.0
Microsoft Analysis Services Client Tools10.0.1600.22
Microsoft Data Access Components (MDAC)3.86.3959
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer8.0.6001.18702
Microsoft .NET Framework2.0.50727.3615
Operating System5.2.3790
Oh it's still an issue!!!!!
April 12, 2011 at 5:19 pm
TheHose (4/12/2011)
Version is SQL Server 2008Microsoft SQL Server Management Studio10.0.2531.0
Microsoft Analysis Services Client Tools10.0.1600.22
Microsoft Data Access Components (MDAC)3.86.3959
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer8.0.6001.18702
Microsoft .NET Framework2.0.50727.3615
Operating System5.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2011 at 11:35 am
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:
April 13, 2011 at 11:39 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply