Backup--Transfer--FTP!

April 22, 2003

Having a standby server in a remote site, copying backup files directly to a Disaster recovery site or other requirements such as transfering files from one location to another using FTP creates the need for creating a FTP batch file using a changing parameter file. In this article, I will walk you through the whole process of FTP by creating and using a dynamic FTP parameter file.

Windows FTP.exe requires a file as a parameter to automate the FTP process. Below, is a typical FTP batch file.

ftp -s:c:\temp\FTP.txt ftp.myclient.com

This FTP batch file gets its parameters from a file called FTP.txt. Below is atypical FTP parameter file.

myuserid
mypassword
CD /u/trannet
binary
put c:\temp\userdatabase.bak
quit

The FTP.txt file is static. In order to make it dynamic with a new backup file name every time, the following code is used.

Step1: Create batch file

Create a batch file c:\temp\ftp.bat with the below given statement on the SQL Server Box.

ftp -s:c:\temp\FTP.txt ftp.myclient.com

Step2: Create a procedure which creates the dynamic parameter file.

Use master
go
Create procedure usp_savefile @userid varchar(200),@password varchar(200), 
@changepath varchar(200), @ftptype varchar(20),@ftpfilename varchar(200), 
@ftpparafile varchar(200), @ftpbatchfile varchar(200)
as
--Created by: MAK
--Created date: 
--Last updated date:
declare @text1 varchar(500)
set @text1 = 'echo '+@userid +">" +@ftpparafile
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '+@password +">>" +@ftpparafile
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '+@changepath +">>" +@ftpparafile
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '+@ftptype +">>" +@ftpparafile
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '+@ftpfilename +">>" +@ftpparafile
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo quit'+">>"+@ftpparafile
exec master.dbo.xp_cmdshell @text1
exec master.dbo.xp_cmdshell @ftpbatchfile

Step 3: Create the procedure which runs the backup command, create the ftp parameter file and transfer the file to the FTP location using FTP.bat

Use master
go
Create procedure USP_ftpbackup @dbname varchar(128),@path varchar(500),
@ftptext varchar(500), @backuptype @varchar(20) as

--Created by: MAK
--Created date: 
--Last updated date:
declare @backupname varchar(500)
declare @x varchar(500)
declare @dateandtime varchar(12)
declare @date datetime
declare @backup varchar(200)
set @date =getdate()
set @dateandtime = convert(varchar(20),@date,112) +case when 
datepart(hh,@date) <10 then '0'+convert(varchar(1),datepart(hh,@date)) else
convert(varchar(2),datepart(hh,@date)) 
end
+case when datepart(mi,@date) <10 then
'0'+convert(varchar(1),datepart(mi,@date)) else
convert(varchar(2),datepart(mi,@date)) 
end
set @backup = "Backup "+@backuptype +" " + @dbname+ " to disk = '"+
@path+@dbname+"_"+@dateandtime+".bak' "
exec ( @backup)
--print @backup
set @backupname ='put '+@path+@dbname+@dateandtime+'.bak'
--Remember to change the parameters according to your requirement
exec usp_savefile  'myuserid','mypassword','CD /u/trannet' ,'binary', 
@backupname,@ftptext,'c:\temp\ftpbackup.bat'