# Backup sql database directly to FTP server

Is it possible from SQL Management Studio to create a backup directly to an FTP server? Basically, instead of choosing the destination to a disk, I sould like to be able to specify an FTP location (server, user, pass) to send the backup file to.

This prevents the backup data from consuming space on the server itself. Network transfer through a shared folder is not an option for security reasons.

-

## migrated from stackoverflow.comApr 4 '13 at 17:08

This question came from our site for professional and enthusiast programmers.

This is really a question for the DBA site, but the short answer is "no". As the documentation says, you can only back up to disk or tape and the only supported network location is a network share. You could look for third-party tools that map an FTP site to a share, or back up databases directly to FTP, or perhaps do something at the storage system level. –  Pondlife Apr 4 '13 at 15:23

If you just want to backup to a non-network path location, then there is now a [tool for backing up to Windows Azure. Also, SQL Server 2014 will allow to backup directly to Windows Azure without a separate tool. This is a viable alternative to trying to backup to a FTP site while eliminating the need to keep a local copy of the backup. Please note the backups can be encrypted which is an obvious must if you are storing the backups off premises.

Otherwise, you may want to find a way to map a FTP site as either a network path or drive. Although I have never used either of these products, FTPUse and NetDrive should allow you to accomplish this goal. There's a blog post on how to map FTP sites as network drives in Windows 7 that might also work for you, but it appears to be an OS-specific solution that might not work for you.

Although these solutions may work, I would strongly recommend that you backup the file locally and then copy it to a FTP site. Drive space is cheap. If you store the backup locally, then you can easily restore from backup without having to download the backups. You should also periodically test restoring your backups to validate that you have good backups. You should also periodically test restoring your backups from wherever you are sending the backups to make sure these are good as well.

Backing up a database is an IO intensive process and backing up to a FTP location even if it is represented as a local drive will probably be very slow. Even if the performance is adequate for your purposes, FTP connections can be unstable and can be interrupted for a variety of reasons. You don't want to have to run another backup just to get the file over the to FTP site. It would be safer to backup to a high speed local drive and upload the file separately. I would strongly recommend finding a way to solve your storage problem rather than going down this road.

-

Not natively, no. The only way you could do this would be to create a job that backs up the database either locally or to a network share and then have another step that runs an FTP command outside of SQL server to upload the file that you just backed up.

-

I haven't done it yet but you can try a powershell script. I found this on experts exchange. You need to put the username and the password on the same line.

declare @workfilename varchar(MAX)


declare @cmd varchar(1000) set @workfilename = 'c:\temp\ftp.txt'

-- create ftp script

select @cmd = 'echo ' + 'username' + ' > ' + @workfilename exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'password' + ' >> ' + @workfilename exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'prompt' + ' >> ' + @workfilename exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'lcd c:\temp\incoming' + ' >> ' + @workfilename exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'mget .' + ' >> ' + @workfilename exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'quit' + ' >> ' + @workfilename exec master..xp_cmdshell @cmd

-- exec ftp script select @cmd = 'ftp -s:' + @workfilename + ' ftp.host.com' exec master..xp_cmdshell @cmd

-- output output NULL NULL Login incorrect. prompt lcd c:\temp\incoming mget .

Try playing with powershell and you should be able to do it.

-

There is another option that I'd recommend if you do this frequently. I've used GoAnywhere to write a very simple script that queries the database and automatically moves it to an FTP server. This can be put on a schedule and email alerts sent when completed or failed. I've used this move a lot of different kinds of data and transform it into what I need at the receiving end. Take a look at GoAnywhere.com for more info.

-

You can backup databases only to a disk or tape. Anyway there are some tools like SQLBackupAndFTP or SQLBackupFree that allows you upload backups to FTP server and you can schedule such jobs with it.

-

## protected by Paul WhiteJan 20 at 7:04

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.