Some time ago I had to transfer SQL Server databases from remote retail locations to central system. At that time whol einfrastructure was operating on Microsoft SQL server 2000 and link in remote locations was 128kb/s ISDN. At that time I thought that the best idea will be backup database to local hard disk on SQL server, RAR it and FTP compressed backup of SQL database to headquater. It did work quite well. So, below is a script I scheduled to be run as part of scheduled job defined on SQL Server Agent.

Before script will be used few things have to be customized:

• location for database backups – @archive_path – default: F:\Backup
• location of WinRAR application – @archive_cmd – default: C:\Program Files\WinRAR
• IP address of FTP server – default: 192.168.10.120
• Username and password for FTP user – default: dbupload/password
• Folder on FTP server for backup upload – default: Site_Backup

As there might ba many remote sites uploading backups, each site can have different folder for data transfer.

Here is script to backup SQL Server database and trasfer backup to FTP server:

DECLARE @fname varchar(255)
DECLARE @fname_backup varchar(255)
DECLARE @fname_archive varchar(255)
DECLARE @archive_path varchar(255)
DECLARE @dbname varchar(255)
DECLARE @archive_cmd varchar(255)
DECLARE @put_cmd varchar(255)

SET @dbname = 'Northwind'
SET @fname = @dbname + '_' + LTRIM(STR(DAY(GETDATE())))  + '.' + LTRIM(STR(MONTH(GETDATE()))) + '.' + LTRIM(STR(YEAR(GETDATE())))
SET @archive_path = 'F:\Backup\'
SET @fname_backup = @archive_path + @fname + '.BAK'
SET @fname_archive = @archive_path + @fname + '.RAR'
SET @archive_cmd = '"C:\Program Files\WinRAR\WinRAR.EXE" a ' + @fname_archive + ' ' + @fname_backup
SET @put_cmd = 'echo put ' + @fname_archive + '>> F:\Backup\ftpcmds'

PRINT @fname_backup
PRINT @fname_archive

BACKUP DATABASE @dbname TO DISK = @fname_backup WITH INIT

EXEC xp_cmdshell @archive_cmd
EXEC xp_cmdshell 'echo open 192.168.10.120> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo user dbupload>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo password>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo cd Site_Backup>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo bin>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo ha>> F:\Backup\ftpcmds'
EXEC xp_cmdshell @put_cmd
EXEC xp_cmdshell 'echo bye>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'ftp -d -i -n -s:F:\BACKUP\ftpcmds'