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

Push FTP with SQL Server

By Steve Jones, (first published: 2001/11/01)

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
Total article views: 36507 | Views in the last 30 days: 13
 
Related Articles
FORUM

Backup Solutions

The best way to backup multiple servers (not just SQL!)

FORUM
FORUM

SQL Server High Avilabilty Solutions

SQL Server High Avilabilty Solutions

FORUM

DR solution

SAN/SQL server DR solution

FORUM

Failing backup can't create backup

single database backup can't create file

Tags
administration    
advanced querying    
backup / restore    
dts    
sql server 7    
t-sql    
 
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