LiteSpeed and xp_delete_file

  • 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.

  • I haven't. HAve you checked the security on the files created each way?

  • 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.

  • I think I remember seeing this as well. It looks for specific extensions. I thought this changed in SP2, have you applied that?

  • 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.

  • 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.

  • Just made a SQL backup on one server, copied the file to another SQL server. The XP_Delete_file removed it just fine.

  • Good to know. They must be reading the file informaiton.

  • 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.

  • Is this a SSIS add-in? Your programmer (or you) interested in writing and article and sharing?

  • 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

  • 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.

  • Ever considered using xp_cmdshell 'insert del command' ? (assuming sql server 2005 is being used).

  • 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

  • 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