... 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;