In my previous articles (Using xp_cmdshell, Using WMI, Using VBScript, Automate DTS Logging, among others), I talked about various technologies you can use to automate SQL Server management. Different situations call for different techniques. Many times, you may find yourself using a combination of those techniques to achieve your objective.For example, you may have a need to periodically transfer your backup files to a remote site for disaster recovery purposes. At the same time, you don't want keep old copies of files at your remote site forever. Therefore you need to remove files that are certain days old site to conserve disk space. In this article, I'll show you some techniques on how to achieve that. For transferring files, I'll show you how you can use xp_cmdshell and stored procedure to do this. To remove files that are certain days old, I'll use VBScript that can do recursive deletion (deleting files in a folder and its sub-folder(s)). You can then create a SQL Server Agent job to automate the whole process. You can use other techniques to achieve the same results, such as WMI. I just want to show you a couple of examples here to get you started.
Script to recursively delete files that are certain days old
In one of my previous articles called Using VBScript to Automate Tasks, I showed you how to delete files in a folder that are certain days old. One limitation of that script is that it only delete files that fits the criterion in the folder only, not files in its sub-folders. I enhanced the script so that the delete will be done recursively, including files in sub-folders.
Option Explicit on error resume next Dim sDirPath Dim iNumberOfDays 'Change the directory path here for your needs. 'You can use UNC path like \\MyServer\MySharedFolder sDirPath = "c:\temp" 'Change number of days here iNumberOfDays = 21 DeleteOldFile sDirPath, iNumberOfDays Sub DeleteOldFile(sRoot, iDaysOld) Dim oFSO Dim oFolder Dim oFileCollection Dim oFile Dim oFolderCollection Dim oSubFolder Set oFSO = CreateObject("Scripting.FileSystemObject") set oFolder = oFSO.GetFolder(sRoot) set oFileCollection = oFolder.Files set oFolderCollection = oFolder.SubFolders 'Walk through each file in this folder collection. 'If it is older than iDaysOld days, then delete it. For each oFile in oFileCollection If oFile.DateLastModified < (Date() - iDaysOld) Then oFile.Delete(True) End If Next 'Walk through each sub folder file in this subfolder collection. 'Do a recursive call to delete files in sub-folders that are iDaysOld 'days old For each oSubFolder in oFolderCollection DeleteOldFile oSubFolder.Path, iDaysOld Next 'Clean up Set oFSO = Nothing Set oFolder = Nothing Set oFileCollection = Nothing Set oFile = Nothing set oFolderCollection = Nothing set oSubFolder = Nothing End Sub
Stored procedure to copy files from one folder to another
You can simply use xp_cmdshell to copy files. However, putting the code in a stored procedure makes it modular and easier to manage. Please note that the /D switch of xcopy will only copy files that have not been copied, thus saving time and bandwidth.
CREATE proc usp_CopyFiles @SourceDirectory varchar(200), @DestinationDirectory varchar(200) as --Created by Haidong "Alex" Ji 05/15/03 --Given source and destination folder, this procedure copies --files --Usage: exec dbo.usp_CopyFiles 'SourceFolder', 'DestinationFolder' declare @DOSCommand varchar(150) set nocount on --Check whether the user supply \ in the directory name if not (right(@SourceDirectory , 1) = '\') set @SourceDirectory = @SourceDirectory + '\' --Check whether the user supply \ in the directory name if not (right(@DestinationDirectory, 1) = '\') set @DestinationDirectory = @DestinationDirectory + '\' --The following DOS command will copy files. The /D switch only copies new files. --If a file exists in the destination folder, it will not try to copy it again. set @DOSCommand = 'xcopy /D ' + '"' + @SourceDirectory + '*.*' + '"' + ' ' + '"' + @DestinationDirectory + '"' print @DOSCommand exec master..xp_cmdshell @DOSCommand GO
Conclusion: putting them all together
To complete the disaster recovery task, you can schedule a task using a SQL Server Agent job. The job should have 2 steps. The first step should be file copying using the above stored procedure. The second step is removal of old files using the above VBScript.Hopefully you can get some ideas on how to automate your SQL Server management tasks. What techniques do you use in your day-to-day management? Please rate this article and use the forum to discuss.