April 14, 2011 at 8:42 am
G.Sudhagar (4/13/2011)
1)first step will delete old backup file based on ur need (days).
you would be well advised to delete files AFTER a successful backup for obvious reasons
TheHose
I use a VB script to delete old files, see below
Option Explicit
'Delete all SQL Server backup files more than 5 days old
Dim oFS, oSQLBackupFol, oFol, oFil
Dim sPattern
sPattern = "*.bak" 'for log backups use *.trn
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("C:\MSSQL\Backup") 'Change this as appropriate
For Each oFol IN oSQLBackupFol.SubFolders
For Each oFil in oFol.Files
If oFil.DateCreated < Now-5 then 'Number of days, Change this as appropriate
If ucase(right(oFil.name, 4)) = ".BAK" then
oFil.Delete
End If
End If
Next
Next
Set oFS = nothing
Set oSQLBackupFol = nothing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 14, 2011 at 8:45 am
I agree to this very much so... thanks for the input....
April 14, 2011 at 9:05 am
See, just check the backup from last night, user database bkp process deleted the correct files using the script, same for the transaction logs but the system database bkp process did the backup but the cleanup script did not work.
This is so very strange, maybe the VB script is the way to go here, seems like the xp_delete has a bug but is intermitted, I don't see that the backup chain is broken in some way. If I had more time to research this with the internals for the Stored Procedure and Microsoft supplied the documentation for the xp_delete_file sp. Then I'd have the tools to fix this..... :pinch:
January 17, 2012 at 8:13 am
I am trying this V script to delete old T-log backup files. but i am looking for better script if anyone has please post here.
Script i am using is:
iDaysOld = 7
strPath = "C:\BACKUP"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
For Each objSubfolder in colSubfolders
Set colFiles = objSubfolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
Next
March 6, 2012 at 1:16 am
hi friends...
why this script dont act in my sql server2008 , dont remove old backups ,plz help me
March 6, 2012 at 4:02 pm
n_navadehgi19 (3/6/2012)
hi friends...why this script dont act in my sql server2008 , dont remove old backups ,plz help me
which script, have you tried mine posted above?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 7, 2014 at 12:34 pm
I created maintenance plan to delete backup files(3rd party backup named SQLSafe) more than 26 hours, but it is not deleting anything. Anyone has any idea or solution?
September 28, 2014 at 2:11 pm
rashedul.chowdhury (1/7/2014)
I created maintenance plan to delete backup files(3rd party backup named SQLSafe) more than 26 hours, but it is not deleting anything. Anyone has any idea or solution?
An alternative to maintenance plans directly created from SQL Server are some external tools. I've also been using Maintenance Plans for some time for my backup schedules combined with my cleanup operations, but almost a year ago I switched from the built-in options from SQL Server to external, third party tools.
I've tried multiple tools as I've generally seen that they are far simpler to use and configure (which leaves less room for errors/mistakes) than any maintenance plan created with Microsoft tools, and they do the general tasks faster and without any errors.
Just to give you a sense of how easy it is to cleanup old backups from the backup folder with just one of the tools I've found I suggest you take a look here: http://sqlbak.com/blog/remove-old-sql-server-backups/ and see for yourself how much simpler this task becomes.
Also, most of the current tools are web based, which from my point of view is a huge advantage! Currently, I don't know of any possibility to access/modify your maintenance plan remotely and for me, this kind of tool helps me finish my job 10 times as fast.
~ Just some guy trying to tune queries ~
September 29, 2014 at 3:31 am
A free comprehensive third party option is Ola Hallengren's solution (http://ola.hallengren.com) which is based on stored procedures, sqlcmd, and SQL Server Agent jobs. We've used it on SQL2008 R2, though it looks like it's supported to run on SQL Server 2005 to 2014 at time of writing. Admittedly, it still uses the xp commands discussed earlier.
Once you've run the provided SQL script, set up the SQL Agent job(s) with an appropriate CleanupTime in hours. Here's an example job step definition based on one I've seen in active use, this one for doing a FULL backup of all databases that clears out backups older than 60 hours (2 and 1/2 days).
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'ALL_DATABASES',@Directory = N'\\SERVER\SHARE\FOLDER', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = 60, @checksum = 'Y' , @LogToTable = 'N'" -b
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply