Schedule your backup transfers

This is a follow-up of my recent article entitled "Automated SQL Server backups".

After automating SQL server backups on a remote server, you will probably want to transfer them to an FTP server and you will want this to be automatically done every day.

Using FTP

Windows command-line FTP.exe has the ability to use scripts like in FTP –script:"C:\backup.ftp" where backup.ftp is a text file containing a series of FTP commands:

open ftp://ftp.yourdomain.tld/
username
password
bin
cd /backup
put "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DBName Mondays.bak"
quit

Accordingly, you can schedule the following VB script to execute daily using windows task scheduler:

Dim ftp, dir1, dir2, s, d, bak, fso, sf, shell, cmd
ftp = ftp://ftp.exe/
dir1 = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" '—- Local dir
dir2 = "/backup" '—- FTP dir (do not include / at the end)
s= "backup.ftp"
d=weekday(date)
select case d
case 1
bak="DBName Sundays.bak"
case 2
bak="DBName Mondays.bak"
case 3
bak="DBName Tuesdays.bak"
case 4
bak="DBName Wednesdays.bak"
case 5
bak="DBName Thursdays.bak"
case 6
bak="DBName Fridays.bak"
case else
bak="DBName Saturdays.bak"
end select

Set fso = CreateObject("Scripting.FileSystemObject")
Set sf = fso.CreateTextFile(dir1 + s, True)
sf.WriteLine("open ftp.yourdomain.tld")
sf.WriteLine("username")
sf.WriteLine("password")
sf.WriteLine("bin")
sf.WriteLine("cd " + dir2)
sf.WriteLine("put " + chr(34) + dir1 + bak + chr(34))
sf.WriteLine("quit")
sf.Close

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -s:" + chr(34) + dir1 + s + chr(34)
shell.Run cmd, 8, true

Workaround when Passive FTP is required

When running the command above, you may get the following FTP error "425 – Could not open data connection to port 2512: connection refused" depending on the infrastructure.

There is a good chance that you need passive FTP. Contrary to what some people say, windows command-line FTP is capable of passive mode, but the "literal PASV" command returns a port number which has to be opened using "literal PORT" and this cannot be easily scripted.

The workaround is to use ncftp software and the following VB script instead of the script above:

Dim ftp, dir1, dir2, d, bak, shell, cmd
ftp = "C:\Program Files\NcFTP\ncftpput.exe"
dir1 = "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" '—- Local dir
dir2 = "/backup" '—- FTP dir (do not include / at the end)
d=weekday(date)
select case d
case 1
bak="DBName Sundays.bak"
case 2
bak="DBName Mondays.bak"
case 3
bak="DBName Tuesdays.bak"
case 4
bak="DBName Wednesdays.bak"
case 5
bak="DBName Thursdays.bak"
case 6
bak="DBName Fridays.bak"
case else
bak="DBName Saturdays.bak"
end select

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -u username -p password ftp.yourdomain.tld " + dir2 + " " + chr(34) + dir1 + bak + chr(34)
shell.Run cmd, 8, true

Downloading backups from your FTP server to your workstation or a local server

A similar approach can be used to schedule backup downloads from the FTP server to your workstation or a local server which has tape backup.

Dim ftp, dir1, dir2, shell, cmd
ftp = "C:\Program Files\NcFTP\ncftpget.exe"
dir1 = "/backup/" '-- FTP dir
dir2 = "C:\BACKUP" '-- Local dir (do not include \ at the end)
Dim bakArray(6)
'-- Sundays
bakArray(0) = "DBName Sundays.bak"
bakArray(1) = "DBName Mondays.bak"
bakArray(2) = "DBName Tuesdays.bak"
bakArray(3) = "DBName Wednesdays.bak"
bakArray(4) = "DBName Thursdays.bak"
bakArray(5) = "DBName Fridays.bak"
bakArray(6) = "DBName Saturdays.bak"

Set shell = CreateObject("WScript.Shell")
cmd = chr(34) + ftp + chr(34) + " -u username -p password ftp.yourdomain.tld " + chr(34) + dir2 + chr(34) + " "

For Each bak In bakArray
'ncftpget is sufficiently intelligent to only download newer files
cmd = cmd + chr(34) + dir1 + bak + chr(34) + " "
Next
cmd = Trim(cmd)
shell.Run cmd, 8, true

What is next?

The above can be improved one step further by launching the script directly from SQL Server jobs using an Execute T-SQL Statement Task which would possibly generate and call the script with master.dbo.xp_cmdshell. This would secure the FTP credentials in SQL Server and launch the transfer immediately at the end of a back-up.