删除SQL Server的旧文件方法汇总

对于SQL Server DBA来讲，备份DB是其基本工作，但我们的存储空间有限，因此需要自动删除历史文件，比如一周之前的备份文件。那我们有哪些方法来实现呢？这里将提供6种以上的方法（大部分为转载）：

方法1： 创建维护计划，里面嵌有删除历史数据的的SSIS任务，配置好后，按照向导可直接设定Agent作业。具体步骤不做演示，自己动手做做，很快就明白怎么回事了。

方法2： 直接新建SSIS包，通过里面的清除任务来实现，包建好后，可创建Agent作业来自动运行包。具体步骤也不做演示。

方法3：使用Maintenance task（转载，原文在这里）

Problem
In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands.  A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.

Solution
In previous tip we took a look at using Windows Scripting (Simple way to find errors in SQL Server error log) to read through the error log files and generate a slimmer error file with just the error messages and the related messages that were created at the same time.  In this tip, we will also be using Windows Scripting to go through each of the subfolders to find files older than a certain timeframe and then delete these files.

Here is the VBScript code.  This was pulled together from a few different code snippets found on the internet. 

There are two parameters that need to be adjusted:

• iDaysOld - specify how many days old the files need to be for the script to delete them
• strPath - this is the folder where the backups are created.

iDaysOld = 7 strPath = "C:\BACKUP" Set objFSO = CreateObject("Scripting.FileSystemObject")  Set objFolder = objFSO.GetFolder(strPath)  Set colSubfolders = objFolder.Subfolders  Set colFiles = objFolder.Files  For Each objFile in colFiles     If objFile.DateLastModified < (Date() - iDaysOld) Then         MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name        'objFile.Delete     End If  Next  For Each objSubfolder in colSubfolders     Set colFiles = objSubfolder.Files     For Each objFile in colFiles         If objFile.DateLastModified < (Date() - iDaysOld) Then             MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name            'objFile.Delete         End If     Next  Next

Setup

• To use this script first create a new text file and copy and paste the above code.  Save this new file as C:\DeleteBackupFiles.vbs or whatever you would like to call it.
• Create another new text file and copy and paste the code below. .(If you rename the file or place it in another folder use this instead of the info below.)  Save this new file as C:\DeleteBackupFiles.bat.

C:\DeleteBackupFiles.vbs

Note: As a safeguard the script just displays a message box with the folder and file name.  To actually delete the files you will need to remove the single quote ' before the two delete lines.:

• 'objFile.Delete

At this point you can just run the BAT file and this will delete any files in the subfolders. 

The way this works is it will delete any files that it finds in the subfolders after the starting point.  It does not care about what kind of files they are, it will delete all files that are older than the timeframe specified.  The script also will delete files in the root folder and any files one subfolder level deep. It does not go beyond the first subfolder level.

So if you specify your backups to be in "C:\Backup" this script will delete all files in the "C:\Backup" folder as well as any files in the first level subfolders as long as the date of the file is older than specified.

This can now be setup as a scheduled job by calling the BAT file.  SQL Server Agent doesn't like running VBScript files directly, so by using a BAT file you can set this up as a scheduled job.

Next Steps

• Use this script as is or modify this to have additional options that meet your needs such as specifying certain types of files.
• The script uses the DateLastModified property, but this could also be changed to the DateCreated property.
• Modify the script to add some logging, so you can see what has been deleted.
• The maintenance plans use XP_DELETE_FILE (2005) and SQLMaint (2000) to delete the older files.  You could look at these options as well.
• Take a look at the other backup scripts to automate your entire backup process.
  • Simple script to backup all databases
  • Automating Transaction Log Backups for All Databases

方法4：使用CLR函数（转载，原文在这里）

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 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        Try              iFileCount = 0           myDirectoryInfo = New