Code snippets : SQL : Backup and FTP Database


When browsing repository of all scripts I did use in past, I found one which might be interesting. What this script does is:

  • Backups database to disk (name of database stored in @dbname, patch for backup stored in @archive_path, name of backup fine stored in @fname)
  • Compresses database backup using WinRAR (full command where to find WinRAR and how ot use it stored in @archive_cmd)
  • Generates set of commands for FTP client (series of xp_cmdshell commands redirecting output to F:\Backup\ftpcmds)
  • Uploads compressed backup to FTP server using command prompt FTP client command

I had that as small procedure executed automatically with SQL Server Agent. Script is for Microsoft SQL Server and I was running it on SQL Server 2000 and 2005. Here is code for that small procedure:


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 = 'dbApplication'
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> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo user dbupload>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo password123>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo cd DB_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'

I hope that will be handy for someone to automate databsae backups and transfers.


Automating file transfer via SFTP i FTPS using WinSCP


Some time ago I received request to automate file transfer between FTP server and Development systems. Both, FTP server and Development system, are Windows-based. FTP server was running FTP over SSL only, so that automatically eliminated built-in Windows FTP command-line app. As I was using WinSCP in the past decided to do quick check if it is possible to use it in batch mode, so I can create script and run it on Windows Task Scheduler to automate whole process.