Script to take database backup, compress it and upload to SFTP

BACKUP

declare @BACKUP varchar(200), @SQL varchar(500), @DBName varchar(100),@path varchar(100)
set @DBName = 'TestDB'
set @path = 'E:\TestDB\'
set @BACKUP = @path + @DBName +'_' + convert(varchar,getdate(),112) + '.bak'
set @SQL = 'BACKUP DATABASE ['+@DBName+'] TO DISK = '''+ @BACKUP + ''''
print (@SQL)
exec (@SQL)

ZIP

DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(500), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = '"C:\Program Files\WinRAR\RAR" a '+@BACKUP+'.rar'+' '+@BACKUP+''
print (@COMMAND)
EXEC XP_CMDSHELL @COMMAND

COPY

EXEC XP_CMDSHELL 'e:\TestDB\backupmove.bat'

This backupmove.bat file contain,

"C:\Program Files\WinSCP\WinSCP.com" -script=e:\Testdb\backupmove.txt

backupmove.txt contain

option batch on
option confirm off
open sftp://username:password@HOSTName:port
cd foldername
option transfer binary
put E:\Testdb\*.rar
close
exit

You can download WinSCP from http://winscp.net/eng/index.php