Push Those Files Away!!!!

Introduction

When DTS was released, I thought this was a useful, but overhyped tool. After all, I'd spent seven

years developing all types of SQL applications that made do with xp_cmdshell for anything that I

couldn't do in T-SQL. Then last year, I started working for a company that used a "Managed" service

for it's website. Bascially this means that we paid some company a rental fee for servers and they

kept the servers running, backed up, patched, etc. in a data center. The data center I used happened to

be in Dallas, which is fine. Except, I live in Denver. Kind of a hassle for hands-on work. Fortunately

we were running on Windows 2000 and DTS can in very handy for building some data

transfer applications.

The Problem

Recently we moved to another data center in Denver and upgraded to SQL Server 2000. Tada!!!!!

Finally, DTS included an FTP task. I eagerly dug into DTS (with the aid

of

Professional SQL Server 2000 DTS) and got ready to develop an automated task to send the

database backups to my development server each night.

I perform local backups from SQL Server and then need to copy these

to a remote server. In the current setup, I wanted to copy them to a utility server on the local network,

but a server that was not part of the domain (for security reasons). This makes transfers using standard

Windows file copy utilities problematic. The solution that I wanted to use was ftp since this is a standard, open solution. At the present time,

all my servers are Windows, but the possibility exists that we may implement some Linux or Unix servers.

One problem: The FTP task only "Gets" files.

How absolutely, completely, totally annoying! How much effort is there to "push" files when you

have already written an object to "Get" them!!!!!!! (SQL Server Team, hint, hint).

The Solution

Never fear! I am not so easily deterred. When I was

using a managed solution, the backups were performed once a day using a maintenance task.

I created a batch file that would ftp the *.BAK files from the backup

directory using the native ftp client in Windows 2000 and a scheduled task,

I transferred this to my office and then had a separate batch file that would

rename it. This worked great since there was only one backup file each day in

the backup directory. In the new data center, however, we had cranked down the

backups and were making one every six hours and keeping the previous 4. Plus I

now needed to transfer copies of the backup files to a secondary server where

they could be backed up to tape more often.

The solution was to leverage the ActiveX Scripting task as well as the

Execute Process task in DTS. And a little understanding of the ftp client and

batch file automation. Let's examine the ftp client first.

The ftp Client

If I wanted to automate the sending of a series of files, I can build a text

file that contains the commands I want the ftp client to run. Of course, to do

this, I need to know the file names of all the files. I then call the ftp client

with the -s option and include the name of my text file. Suppose I wanted to

send two files from my system (c:\test1.txt and c:\test2.txt) using ftp, I could create a text file that

contains the following:

open ftp.test.com
MyUser
MyPwd
put c:\test1.txt
put c:\test2.txt
quit

I'd then save this as "c:\ftpauto.txt". If I then run the following

command:

ftp -s:"c:\ftpauto.txt"

This command will connect to the ftp.test.com server. Once it connected, the next two lines send the user

name and password. The remaining lines (before the quit) send specific files to the server. The "quit" command

ends the session.

The test1.txt and test2.txt files would be sent to the ftp site automatically. This gets us the first part of the

solution. We now know how to automate an ftp "put". Now on to the next part, determing which files to

transfer.

ActiveX Scripting

I am sure most of you can guess this part, but it is

a fairly simple exercise in the use of the FileSystemObject. If you have never used it, the

FileSystemObject allows you to manipulate files and folders in the file system of a

computer. This object is very powerful and includes methods for creating, deleteing, moving, copying, listing, etc. for both

files and folders.

For my purposes, I needed to scan all the backup folders and get the latest

backup files under each folder as well as any transaction backups that had not been

transferred. The last transfer date was stored in a global variable in the package. This will

be described below in The DTS Package. The other scripting was

implemented in the VB Script below:

'**********************************************************************
' Copy latest backups
'************************************************************************
Function Main()
On Error Resume Next
Dim strDestPath, fNewFile, strSourcePath, fOldFile, sBackup
Dim fso, f, f1, fc, fcreated, fname, fldrItem, fldrName
Dim objTxtFile, baseDate
' Initialize the variables
strSourcePath = "c:\Program Files\MSSQL\Backup"
strDestPath = "c:\SQL_Backups"
fcreated = DTSGlobalVariables("LastXfr").value
BaseDate = CDate( fcreated)
Set fso = CreateObject("Scripting.FileSystemObject")
' Create the Text File
Set objTxtFile = fso.CreateTextFile( strSourcePath & "\ftpSend.txt", TRUE)
' Write the Header
objTxtFile.writeline( "open 192.168.1.104" )
objTxtFile.writeline( "FTPSQLBackup" )
objTxtFile.writeline( "SendItNow" )
objTxtFile.writeline( "cd SQLBackup" )
Set f = fso.GetFolder(strSourcePath)
For Each fldrItem in f.SubFolders
Set fc = fldrItem.Files
fldrName = fldrItem.name
fname = " "
For Each f1 in fc
If f1.DateCreated > BaseDate Then
objTxtFile.writeline( "put """ & strSourcePath & "\" & fldrname & "\" & f1.name & """")
End If
Next
Next
objTxtFile.writeline( "quit" )
objTxtFile.Close

Set objTxtFile = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function

This script really functions in three parts, which I will describe below.

The first part of the script writes the header portion of the ftp file. This

part includes the lines

' Create the Text File
Set objTxtFile = fso.CreateTextFile( strSourcePath & "\ftpSend.txt", TRUE)
' Write the Header
objTxtFile.writeline( "open 192.168.1.104" )
objTxtFile.writeline( "FTPSQLBackup" )
objTxtFile.writeline( "SendItNow" )
objTxtFile.writeline( "cd SQLBackup" )

These lines create the file and then write the ftp server address, the user name, the password, and

then change the destination directory.

The next section is designed to loop through all the folders in my backup folder (stored in strSourcePath).

First I create a handle to work with and then set the "f" variable to the folder handle. I then

Set f = fso.GetFolder(strSourcePath)
For Each fldrItem in f.SubFolders
Set fc = fldrItem.Files
fldrName = fldrItem.name

The DTS Package

The DTS package is built by including the ActiveX task above. In addition, there is a global variable in the

package that stores the last transfer date for the set of backup files. I populate this using the dynamic properties

task to read a datetime value from a table and update the global variable. This allows me to send files that were

created after a particular datetime.

The Job

The last part of the solution was to implement a job under SQL Agent. This job consisted of two steps: one to

run the DTS package and create the ftp file and the second to run the ftp client with the -s parameter and the file

name created in part 1.

Conclusion

I know there are any number of COM wrappers over

utilities that will implement this same functionality. However, these solutions

have two downfalls for me:

1. Using a third party utility adds complexity,

   requires installation, and adds additional potential that something will

   fail on my server.

2. These solutions cost money. And if I can develop

   my own solution, I'd rather do that.

This solution gives me good control over the transmission of the ftp files. It also keeps the solution inside

SQL Server. I am taking advantage of only components that exist on all my SQL Servers without having to install any

additional software.

As always, I am sure some of you will have great ideas to extend this technique or will point out any flaws. Please

use the "Your Opinion" button below and please rate this article.

Steve Jones

October 2001