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.