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'
Next 1 2
|