October 30, 2007 at 6:03 pm
I have the following statement:
EXECUTE master.dbo.xp_delete_file 0,N'M:\Backups\db_admin',N'bkp', N'2007-10-28 18:34:26'
For files created with native SQL backup this deletes the files just fine, but for files created using LiteSpeed it simply fails to delete the files. Anyone run into this?
Thanks.
October 31, 2007 at 9:19 am
I haven't. HAve you checked the security on the files created each way?
October 31, 2007 at 9:23 am
I did some more testing this morning.... Looks like that XP only deletes files that where created by a SQL maintenance plan, or by SQL native backups. Guess that is what I get for using an undocumented feature.
October 31, 2007 at 8:50 pm
I think I remember seeing this as well. It looks for specific extensions. I thought this changed in SP2, have you applied that?
November 1, 2007 at 6:40 am
It's on SP2, and I tried it with the same extension name as well.
Before running my delete statement right now I have two files in a directory:
AndersTest.bak backed up with SQL, creation time of 11/1/2007 @ 7:21 AM
AndersTest_DB_20071101130.BAK backed up with LiteSpeed, creation time 11/1/2007 @ 2:14 AM
After running:
EXECUTE master.dbo.xp_delete_file 0,N'M:\Backups\AndersTest',N'bak', N'2007-11-01 07:30:22'
The LiteSpeed file is left.
To verify that it was not something in the LiteSpeed backup preventing it from being deleted, I created a file in notepad and saved it as AndersText.bak, this file did not get deleted either.
To make sure security is not the problem I logged into the desktop on the server using the same login as the SQL Agent uses, I could then delete both the LiteSpeed backup and the fake backup file.
My only conclussion is that xp_delete_file either looks at the content of the file, or looks into the backup information tables to see if it was backed up using SQL or not. I do believe the first to be the case since I have a job that sends me a list of the last backup data for each database and it clearly shows me the dates that LiteSpeed backed up the databases.
November 1, 2007 at 10:26 am
My guess is it's looking at the backup history in MSDB, maybe even logging. I should test this by removing some backup info from the tables or replacing the file with one of a different type, same name.
November 1, 2007 at 11:32 am
Just made a SQL backup on one server, copied the file to another SQL server. The XP_Delete_file removed it just fine.
November 1, 2007 at 2:45 pm
Good to know. They must be reading the file informaiton.
November 2, 2007 at 6:29 am
Solution found 😉
Make my own (well, one of our programmers did) C# program that will do what I want. Guess I could have made a CLR program and exposed it to SQL, but decided to keep it simple and make it a regular EXE.
November 2, 2007 at 9:40 am
Is this a SSIS add-in? Your programmer (or you) interested in writing and article and sharing?
November 2, 2007 at 10:11 am
Not very fance at all, using xp_cmdshell 🙂 We considered making it into a CLR, but neither him or I am completely comfortable opening up that can of worms yet.... I would say that this is about the first time I have even considered using it though.
xp_cmdshell 'c:\bin\deletefile.exe "M:\Backups\AndersTest" "bak" "2007-10-31 11:08:31"'
I have this wrapped in a cursor that dynamically builds the above string and executes it.
If any interrest I can see if it is ok by him to publish the code for the DeleteFile.exe and write an article on it.
set quoted_identifier off
go
-- modified Anders 10/26/2007 to also cleanup after LiteSpeed backups
declare @sql varchar(max)
declare @runtime varchar(22)
select @runtime = convert(varchar(22),dateadd(dd,-2,getdate()),120)
declare @Path varchar(100)
declare @extension varchar(5)
set @extension = 'bkp'
DECLARE DBNames CURSOR
READ_ONLY
FOR select name from sys.databases
where --name not in ('anderstest' , 'tblCheck', 'Northwind','tempdb')
name = 'anderstest'
DECLARE @name sysname
OPEN DBNames
FETCH NEXT FROM DBNames INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @Path = 'M:\Backups\' + @name
set @extension = 'bak'
select @sql = 'xp_cmdshell ''c:\bin\deletefile.exe "' + @Path + '" "' + @extension + '" "' + @runtime + '"'''
-exec (@SQL)
END
FETCH NEXT FROM DBNames INTO @name
END
CLOSE DBNames
DEALLOCATE DBNames
GO
November 4, 2007 at 9:38 am
I think it would be interesting, especially pointing out the issues with delete_file.
If you're game, I'd like to publish. If not, I can find someone else to do it.
November 5, 2007 at 10:57 am
Ever considered using xp_cmdshell 'insert del command' ? (assuming sql server 2005 is being used).
January 9, 2008 at 4:14 pm
I delete my LiteSpeed backup files with this code in a Script Task in a maintenance plan package. It is configured by the variables BackupRoot (parent dir for all db backup dirs) and BackupAgeLimit (age in hours). I use hours for the age limit so I can use a 1/2 day offset, i.e. 36 or 60 hours instead of 2 or 3 days.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sRoot As String = Dts.Variables("BackupRoot").Value.ToString
Const BackupFilePattern As String = "*_backup_20*.BAK"
' Get ADO.NET connection from connection manager
Using cnn As SqlClient.SqlConnection = _
DirectCast(Dts.Connections("(local).master").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)
' Set up a command to check filenames and age in backup history
Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
With cmd
.Connection = cnn
.CommandText = "SELECT COUNT(*) AS EligibleForDelete FROM msdb.dbo.backupmediaset AS ms " _
& "INNER JOIN msdb.dbo.backupmediafamily AS bmf ON ms.media_set_id = bmf.media_set_id " _
& "INNER JOIN msdb.dbo.backupset AS bs ON ms.media_set_id = bs.media_set_id " _
& "WHERE (bs.backup_start_date < DATEADD(hour, @age, GETDATE())) AND (bmf.physical_device_name = @file)"
.Parameters.Add("@age", SqlDbType.Int)
.Parameters(0).Value = CType(Dts.Variables("BackupAgeLimit").Value, Int32)
.Parameters.Add("@file", SqlDbType.NVarChar, 1023)
End With
' Find every .BAK file in every subfolder
For Each sFile As String In Directory.GetFiles(sRoot, BackupFilePattern, SearchOption.AllDirectories)
Try
' Only consider files that have been written to tape (archive bit is not set)
If (File.GetAttributes(sFile) And FileAttributes.Archive) = 0 Then
' Check the age of the backupset
cmd.Parameters(1).Value = sFile
If CType(cmd.ExecuteScalar(), Int32) = 1 Then
' If the file is a backup file, has been written to tape, and is old enough, delete it
File.Delete(sFile)
End If
End If
' An empty Catch block is used to ignore spurious errors
Catch
End Try
Next
End Using ' cmd
End Using 'cnn
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
July 23, 2008 at 4:34 am
Where can this program be downloaded?
Is there any patch from Microsoft for this XP to delete other files also?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply