Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 22, 2003


By Staff

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

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

CD /u/trannet
put c:\temp\userdatabase.bak

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

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

Use master
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)
--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
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
+case when datepart(mi,@date) <10 then
'0'+convert(varchar(1),datepart(mi,@date)) else
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', 

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

Thanks for your registration, follow us on our social networks to keep up-to-date