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 only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
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.
... 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;
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.