5

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.

1
  • 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, 2013 at 15:23

6 Answers 6

5

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.

1
  • 1
    There is a really good reason to do an offsite backup. It's not a matter of storage. My client was hacked a few days back and all of their on-site backups were rendered useless. Without an offsite solution we would have been dead.
    – Rob
    Nov 29, 2017 at 20:28
2

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.

2

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.

2

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.

1

... instead of choosing the destination to a disk, I should 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.

Others have interpreted these statements to mean that there is not enough disk space to even do the initial backup to, prior to sending the backup file to a remote location. I am interpreting these statements as meaning that while there is enough space to at least take one FULL backup, there isn't enough space to store a week's worth of FULL, DIFF, and LOG backups.


Is it strange that there are already 5 answers and not one mention of SSIS? While SSIS is never my first choice when needing to automate a process, it does come with SQL Server (though I guess not the Express and Web editions) and certainly seems capable of handling this type of operation. The three required pieces seem to be:

OR, if you don't want to deal with SSIS, you can get the functionality for doing the FTP and deleting the backup file from SQLCLR. This would be handled by FtpWebRequest and File.Delete. If you don't want to bother with coding / compiling / loading the Assembly, the functions required to handle this operation are available in the SQL# library (which I am the author of, and these particular Network-related and File System-related functions are not available in the Free version). The following is an example of using three of the SQL# functions to automate this process via T-SQL (i.e. can be a Stored Procedure and/or SQL Agent T-SQL job step):

DECLARE @BackupFolder NVARCHAR(500) = N'C:\TempBackup',
        @BackupFilePath NVARCHAR(500),
        @BackupURL NVARCHAR(1000) = N'ftp://URL.tld';

SET @BackupFilePath = @BackupFolder + N'\DatabaseName_FULL_YYYYMMDD.bak';

BEGIN TRY

  EXEC SQL#.File_CreateDirectory @BackupFolder; -- make sure folder exists for the backup

  BACKUP DATABASE [{database_name}]
      TO DISK = @BackupFilePath
      WITH CHECKSUM, COMPRESSION, DESCRIPTION = N'My backup';

  EXEC SQL#.INET_FTPPutFile @BackupURL, N'User', N'Pass', N'PUT', 0, 1, @BackupFilePath;

  EXEC SQL#.File_Delete @BackupFilePath; -- remove the local backup file

END TRY
BEGIN CATCH
  -- handle the error
END CATCH;
0

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.

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