Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Different Techniques for SQL Server Automation

By Haidong Ji,

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.

Total article views: 17296 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Dynamic creation of Insert, Update, Delete Stored procedures

Automates the creation of INSERT, UPDATE & DELETE stored procedure of a table

FORUM

Create, Rename, Delete file folder thru SQL

Create, Rename, Delete file folder thru SQL

FORUM

Deleting snapshot folder

i get access denies error message when try to delete files from snapshot folder

FORUM

Snapshot Replication - deletion of snapshot folders from OS drives

Snapshot Replication - deletion of snapshot folders from OS drives

FORUM

Urgent:VB Script to delete 2-days old backup automatically

Automation Script to delete older backup in Network drive

Tags
administration    
disaster recovery (dr)    
dts    
sql server 7    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones