CLR function to delete older backup and log files in SQL Server

By:   |   Comments (3)   |   Related: > Backup


In a previous tip we looked at how to put together a CLR function for sorting text data.  In addition, we have also written tips about how to mimic the functionality of maintenance plans without having to use a maintenance plan.  In one of these previous tips, "Maintenance task to delete old backup files" we outlined how to delete older backup files by using a VB Script.  To take this deleting of older files a step further, this tip will look at this same task to remove older backup and log files, but this time using a CLR function.


Before we get started the first thing that needs to be done is that you need to enable the CLR on your SQL Server.  This can be done by using the code below.

sp_configure 'show advanced options', 1;  
sp_configure 'clr enabled', 1;  

In this CLR function we are going to pass in a few parameters such as:

  • File path
  • Days to keep files
  • File Extension

and return a count of the number of files that were deleted as well as delete the actual files.

Step 1 - CLR code

The first thing we need to do is to write the CLR code for this.  This could be written in either C#.NET or VB.NET.  In this example we are using VB.NET.

The following code has a Class (CLRFunctions) and a Function (DeleteFiles).  The function takes three parameters and returns an integer value.

Copy and save the code below in a file called: C:\CLRFunctions.vb

Imports System.IO  

Public Class CLRFunctions     

   Public Shared Function DeleteFiles(sPath As String, iDaysToKeep As Integer, sFileExtension As String) As Integer    

      Dim arrFiles As Array  
      Dim dateToday As Date  
      Dim myFileInfo As FileInfo  
      Dim myDirectoryInfo As DirectoryInfo  
      Dim iFileCount As Integer  

         iFileCount = 0  

         myDirectoryInfo = New DirectoryInfo(sPath)  

         arrFiles = myDirectoryInfo.GetFiles()  

         dateToday = DateAdd("d", -iDaysToKeep, Today)  

         For Each myFileInfo In arrFiles  
            If myFileInfo.Extension = sFileExtension And myFileInfo.LastWriteTime < dateToday Then  
               iFileCount = iFileCount + 1                  
            End If  

         Return iFileCount  
         Return 0          
      End Try 

   End Function    

End Class

Step 2 - Compile CLR Code

In order to use this code, the code has to be compiled first. 

The following command is run from a command line to compile the CLR code using the vbc.exe application.  This is found in the .NET 2.0 framework directory. I also tried .NET 4.0 framework and this worked as well. The path may be different on your server or desktop.  Also, this code should be compiled on the machine where the code will run.

So from a command line run a command such as the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\CLRFunctions.vb

The code should now be compiled in a file called: C:\CLRFunctions.dll

Step 3 - Create Assembly and Function

After the code has been compiled you need to create the assembly and the function with SQL Server.  To do this, run these commands in the database where you want to use the function. 

The assembly ties an internal object to the external DLL that was created and the function is similar to a normal SQL Server function.

For the function you will see three components that are referenced CLRFunctions.CLRFunctions.DeleteFiles.

  • CLRFunctions - the assembly reference
  • CLRFunctions - the class reference in the VB code
  • DeleteFiles - the function reference in the VB code
CREATE ASSEMBLY CLRFunctions FROM 'C:\CLRFunctions.dll'  

CREATE FUNCTION dbo.DeleteFiles    
 @FolderPath AS NVARCHAR(100), 
 @DaysToKeep AS integer, 
 @FileExtension AS NVARCHAR(50)    
RETURNS integer   
AS EXTERNAL NAME CLRFunctions.CLRFunctions.DeleteFiles 

If you get error messages when trying to compile the code you may need to alter the database using the following command and then try again to create the assembly and the function.

ALTER DATABASE test1 SET trustworthy ON

Step 4 - Test It

To test the function, run the following SELECT statement based on the values you want to pass into the function. Note the file extension is case sensitive.

In this example we are deleting files in the "C:\Backups" folder that are 14 days or older and have an extension of ".BAK"

SELECT dbo.DeleteFiles('C:\Backups', 14, '.BAK') AS FilesDeleted 

In this example we are deleting files in the "C:\Backups" folder that are 7 days or older and have an extension of ".trn"

SELECT dbo.DeleteFiles('C:\Backups', 7, '.trn') AS FilesDeleted 

In this example we are deleting files in the "C:\Backups" folder that are 14 days or older and have an extension of ".LOG"

SELECT dbo.DeleteFiles('C:\Backups', 14, '.LOG') AS FilesDeleted

Step 5 - Cleanup

To get rid of the code you will need to delete the DLL that is created from the compile step as well as the VB file that was created. 

In addition, run this T-SQL code to drop the objects that were created.

DROP FUNCTION dbo.DeleteFiles  


That's all there is to creating a CLR function to delete older backup files. This function as built only looks in one directory as well as only deals with one type of file extension at a time, so these are some things that could be done to improve this code.

One thing to note is that when the files are deleted they are not put into the recycle bin, so make sure you test this in a test environment to make sure you understand what is occurring before you implement this on your production servers.

This code has been tested successfully with SQL Server 2017, so should work fine with all versions that support CLR.

Next Steps
  • Give this example a try and see what other functions you could write that could take advantage of the CLR
  • If you have some enhancements to this function that you would like to share, please post them below.
  • If you don't know how to write either VB or C# now is the time to begin learning.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, November 16, 2019 - 3:42:40 PM - Jeff Moden Back To Top (83107)

Nice article on how to do this with a CLR, Greg.  Thanks for taking the time to write it up and post it.  It also provides some handy instructions for how to make a CLR that will do other things.

If someone is reading this and sighing a bit because their company policy won't allow them to create CLRs or they're a DBA that doesn't even know how to fire up the (for example) IDE for VBA or whatever, there's still a way to do this for SQL Server backup files.  Do a search for "xp_delete_file".  It's an undocumented extended stored procedure that has been around almost forever and isn't likely to go away without some easy replacement.

You can even build it into a maintenance plan, if you'd rather.  Just start building a maintenance plan and look for the "Maintenance Cleanup Task"

Thursday, June 7, 2018 - 9:25:20 AM - Greg Robidoux Back To Top (76148)

Hi Nani,

what kind of errors are you getting?


Thursday, June 7, 2018 - 8:12:19 AM - Nani Back To Top (76146)

we have some clr functions to delete ,copy files from folder and build it in sql 2008 r2 and clr version 2.0 now we have upgraded sql to 2012 sp4 so how can we upgrade clr functions from 2.0 version to 4.0  version as we are getting some error while using clrs in 2012.

get free sql tips
agree to terms