Zip and FTP DB Backup using SQL CLR c#

I have been looking around for a while for a way to zip and ftp DB backups out of my production server from within the SQL engine without using xpcmdshell.
xpcmdshell creates huge security holes and should not be used. Extended procs are also likely to end in tears due the high chance of memory leaks if they ever encounter a glitch.
After looking around on Google I couldn''t find anything so ended up writing a C# CLR procedure which gzips a backup and then ftps it to a specified destination. (gzip libraries are now built into .NET 4 and are actually much more efficient and less bother than using windows native zip format.)
This means you can zip and ftp any file using a stored procedure within a standard SQL batch.
The proc requires ''external access'' to the file system and the remote server so to ensure the code doesnt create security weakness''s i have created it with a certificate (asymmetric key) which is mapped to a specific sql login. This ensures that the dll can only be accessed by this SQL login and no other process can take advantage of its increased security context.
First one needs to create dll.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.IO.Compression;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void zipandftp(SqlString FTPAddress, SqlString user, SqlString pwd, SqlString filePath)
    {
        string zippedbaktobeftp = compress(filePath);
        Upload(zippedbaktobeftp, FTPAddress.ToString(), user.ToString(), pwd.ToString());
    }
    private static string compress(SqlString filename)
    {
        string zippedbak = filename.ToString() + ".gz";          // Get bytes from input stream          FileStream inFileStream = new FileStream(filename.ToString(), FileMode.Open);     
        FileStream outFileStream = File.Create(zippedbak); GZipStream compressedStream = new GZipStream(outFileStream, CompressionMode.Compress); CopyStream(inFileStream, compressedStream);
        compressedStream.Close(); outFileStream.Close(); inFileStream.Close(); return zippedbak;
    }
    private static void CopyStream(Stream input, Stream output)   
                    {          byte[] buffer = new byte[32768];          while (true)          {              int read = input.Read(buffer, 0,buffer.Length);              if (read <= 0) return;        
                          output.Write(buffer,0, read);          }      }
    private static void Upload(string filename, string ftpServerIP, string ftpUserID, string ftpPassword)
    {
        FileInfo fileInf = new FileInfo(filename); string uri = "ftp://" + ftpServerIP + "/" + fileInf.Name; FtpWebRequest reqFTP;
        reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + fileInf.Name));
        reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword); reqFTP.KeepAlive = false; reqFTP.Method = WebRequestMethods.Ftp.UploadFile;
        reqFTP.UseBinary = true; reqFTP.ContentLength = fileInf.Length;            // The buffer size is set to 2kb     
        int buffLength = 2048; byte[] buff = new byte[buffLength]; int contentLen; FileStream fs = fileInf.OpenRead(); Stream strm = reqFTP.GetRequestStream(); contentLen = fs.Read(buff, 0, buffLength); while (contentLen != 0) { strm.Write(buff, 0, contentLen); contentLen = fs.Read(buff, 0, buffLength); } strm.Close(); fs.Close();
    }
};

Once you have buit this assembly place it in the root of C on your SQL server. You now need to create the asymmetric key and its mapped login and db user.

USE master  
GO  
-- First Create the Asymmetric Key from the Assembly  
CREATE ASYMMETRIC KEY SQLCLR_ExampleKey  FROM EXECUTABLE FILE = ''C:ftpzip.dll'' 
GO    
-- Create the Login from the Asymmetric Key  
CREATE LOGIN SQLCLR_ExampleLogin FROM ASYMMETRIC KEY SQLCLR_ExampleKey  
GO    
-- Grant the External Access Priviledge to the Login  
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLR_ExampleLogin  
GO    
USE [your_db]  
GO  
-- Add a database user in the SQLCLR_Net Database for the Login  
CREATE USER [SQLCLR_ExampleLogin] FOR LOGIN [SQLCLR_ExampleLogin]

Now create the proc

CREATE PROCEDURE [dbo].[zipandftp]
    @FTPAddress [nvarchar](4000),
    @user [nvarchar](4000),
    @pwd [nvarchar](4000),
    @filePath [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ftpzip].[StoredProcedures].[zipandftp]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'zipandftp.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'zipandftp'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'17' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'zipandftp'
GO

Now you have you assembly and the CLR proc you just need the script which creates the backup and calls proc with the ftp credentials

DECLARE @bak  NVARCHAR(255)
-- this bit of date mangling gives us the same backup name format generated by management studio scheduled backups. Which is nice. 
SET @bak  = N'C:\SQL\'+'your_db'+ CONVERT(varchar(255),getdate(),112)+ REPLACE(LEFT(CONVERT(TIME,GETDATE()),5),':','')+'.BAK'
print 'beginning backup'
BACKUP DATABASE [your_db] TO  DISK = @bak
WITH NOFORMAT, NOINIT,  NAME = N'your_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
print 'finsihed back now ftp'

exec dbo.zipandftp '192.168.1.1', 'someuser', 'somepassword', @bak

This will backup your db and move it your ftp location. No SSL is on the FTP connection in this example although the C# Upload function can be easily extended to do this. Generally ftp location should always only be exposed to the ip of your source server so even with the password external access is blocked by the firewall.