Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 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
add comment

6 Answers 6

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

SQL Server Management Studio does not have such feature. The only way is using some third-party backup software. EMS SQL Backup has the ability to compress backup and upload it to FTP servers or network locations.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.