by
MAK [Muthusamy Anantha Kumar]
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:tempFTP.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:tempuserdatabase.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:tempftp.bat with the below given statement on the SQL Server Box.
ftp -s:c:tempFTP.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:tempftpbackup.bat’