Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > Backup

Attend these FREE MSSQLTips webcasts >> click to register


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

Solution
If you have not yet built a CLR function, please refer to this tip for what needs to be done for the initial setup.

CLR Functions - Getting started with a string sort function


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.


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 StringiDaysToKeep As IntegersFileExtension As StringAs Integer   

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

      
Try    
         iFileCount 


         myDirectoryInfo 
= New DirectoryInfo(sPath

         
arrFiles myDirectoryInfo.GetFiles() 

         
dateToday DateAdd("d", -iDaysToKeepToday

         
For Each myFileInfo In arrFiles 
            
If myFileInfo.Extension sFileExtension And myFileInfo.LastWriteTime dateToday Then 
               
myFileInfo.Delete() 
               
iFileCount iFileCount 1                 
            
End If 
         Next 

         Return 
iFileCount 
          
      Catch
         
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.  This 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' 
WITH PERMISSION_SET UNSAFE 
GO

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

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.

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 
GO
DROP ASSEMBLY CLRFunctions
GO

 

Summary

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.

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 in this forum http://blogs.mssqltips.com/forums/t/65.aspx
  • If you have CLR functions that you want to share with the rest of the MSSQLTips.com community, please send them to tips@mssqltips.com so we can post them for others to use or post them in the forums. 
  • If you don't know how to write either VB or C# now is the time to begin learning.

 



Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools