Advertisement

Tutorials

Home Tutorials Advanced Tutorial

Backup--Transfer--FTP!

2.3/5.0 (3 votes total)
Rate:

MAK [Muthusamy Anantha Kumar]
December 22, 2004


MAK [Muthusamy Anantha Kumar]
MAK [Muthusamy Anantha Kumar] works as SQL Server DBA (more than 9 years).
MAK [Muthusamy Anantha Kumar] has written 1 tutorials for CGIDir.
View all tutorials 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:\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 '[email protected] +">" [email protected]
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '[email protected] +">>" [email protected]
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '[email protected] +">>" [email protected]
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '[email protected] +">>" [email protected]
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo '[email protected] +">>" [email protected]
exec master.dbo.xp_cmdshell @text1
set @text1 = 'echo quit'+">>"[email protected]
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 "[email protected] +" " + @dbname+ " to disk = '"+
@[email protected]+"_"[email protected]+".bak' "
exec ( @backup)
--print @backup
set @backupname ='put '[email protected][email protected][email protected]+'.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


Add commentAdd comment (Comments: 0)  

Advertisement

Partners

Related Resources

Other Resources

image arrow