July 22, 2008 at 8:09 am
Hi
I have looked on the forum for a similar problem but couldnt find one.
I want to delete all sqb backups older than 5 days.
I am using the following code which worked before we migrated our backup strategy to RedGate.
DECLARE @CLEANUPDATETIME AS VARCHAR(19)
SET @CLEANUPDATETIME = convert(varchar,Dateadd(day,-6,getdate()),120)
EXECUTE master.dbo.xp_delete_file 0,N'D:\DWH RedGate Backups\',N'sqb',@CLEANUPDATETIME,1
This code does not generate any errors and does not delete anything. I am assuming that since this sp takes a file extension that it actually does something with it.
If I change the extension parameter to 'bak' then it deletes all of the *.bak files which are older than the specified date.
Can anyone perhaps advise me as to how I can look at cleaning up my redgate backups before I run out of space on one of my prod servers.
Thank you!!
July 22, 2008 at 8:15 am
Upon seraching the net further I see that master.dbo.xp_delete_file only deletes native SQL backups (despite allowing for a file extension parameter which makes no sense...)
Is there perhaps a RedGate command that can be used to clean up these backups or am I going to have to break out the MS-DOS again?
July 22, 2008 at 8:40 am
The red-gate extended stored procedure has a paramter to remove old files.
Something like this:
EXEC master..sqlbackup -SQL "BACKUP DATABASES [model]
TO DISK = 'E:\\MSSQL\BACKUP\\model\ '
WITH NAME = ' ',
DESCRIPTION = ' ',
COMPRESSION = 2,
ERASEFILES = 1,
FILEOPTIONS = 1,
THREADS = 1"
For the detailed parameter options see the documentation.
[font="Verdana"]Markus Bohse[/font]
July 22, 2008 at 1:32 pm
Thanks for the reply.
I did manage to find that code earlier but am I correct in understanding that in order to cleanup the backups you actually need to take a backup of the same database? (as per the code you provided?)
July 22, 2008 at 1:40 pm
One more question:
You mentioned documentation for RedGate sqlbackup?
Our senior DBA has the left the company I am not aware of us having any documentation for this product. We have over 20 RedGate SqlBackup licences running on our DEV and PROD servers and I am pusshing for even more servers to adopt it.
Can anyone point me in the direction of some documentation for this product?
July 23, 2008 at 10:41 am
About the backup code, in most cases you usually want to take a new backup first before deleteing the old files. I'm not sure if there's also an option to delete the files without taking a new backup, but I do remember that there's an option to delete the old files first before making the new backup. That's usefull if you have only limited diskspace.
About the documentation if you install the Red-Gate server (GUI) and open HELP you should find all you need. In case your company only installed the client, you can download a trial version from Red-gate. It contains the documentation as well.
[font="Verdana"]Markus Bohse[/font]
July 24, 2008 at 7:30 am
I had problems with Microsoft cleaning up report .txt files on our systems that were generated by SQL backup routines. The backups are with the maint plans and can be cleaned up easily.
We ended up with this script to do cleanups on our systems via scheduler and saved the script as a vbs from notepad. It is a hack but it works. If all your backups are on one folder location then it is easier.
iDaysOld = 30
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "L:\MSSQL\REPORTS"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
July 24, 2008 at 7:39 am
Thanks for the replies guys.
I'm sure this info will help me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply