ARTICLES

Home  > Articles  >  Exporting your SQL Server 2005 Backup to an FTP location
Exporting your SQL Server 2005 Backup to an FTP location

Now that we have access to tools like SQL Server 2005/8, you might ask "Why would I need such rudamentary form of backup as FTP?".
However, in many scenarios, mostly small scale environments where a company would like to take advantage of their FTP server and backup their site, intranet, or blog - this still makes sense. It's definately not the best practice for large databases and critical data, so make sure to evaluate your particular case.

As you may or may not know, when you create a mainenance plan in SQL you can not specify FTP location as a file destination. Well, there is a workaround for exporting your backup to an FTP location.
Few steps below provide solution and code that will take care of automatic export of your backup at predefined time. Parts of the solution can be also used for other scenarios. We will assume you are using SQL Server 2005 and have access to SQL Management Studio.
STEP 1.  Create a backup job (which is out of scope of this article and I assume you know how to do).
STEP2. Ensure the advanced configuration options are turned on and CMD shell is allowed from SQL. This can be done by executing the following commands: 

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
sp_configure 'xp_cmdshell', '1'
GO
RECONFIGURE

 STEP 3. Crate the following procedure inside your master database. This procedure will take care of the FTP upload and will be called from within your backup task (discussed in the next step).
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO
 
Create procedure s_ftp_PutFile
@FTPServer varchar(128) ,
@FTPUser    varchar(128) ,
@FTPPWD           varchar(128) ,
@FTPPath    varchar(128) ,
@FTPFileName      varchar(128) ,
@SourcePathvarchar(128) ,
@SourceFilevarchar(128) ,
@workdir    varchar(128)
as
 
declare     @cmd varchar(1000)
declare @workfilename varchar(128)
     
      select @workfilename = 'FTP_Upload.log'
     
      -- deal with special characters for echo commands
      select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
      select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
      select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
      select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')
     
      select      @cmd = 'echo '                            + 'open ' + @FTPServer
                  + ' > ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
      select      @cmd = 'echo '                            + @FTPUser
                  + '>> ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
      select      @cmd = 'echo '                            + @FTPPWD
                  + '>> ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
      select      @cmd = 'echo '                            + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
                  + ' >> ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
      select      @cmd = 'echo '                            + 'quit'
                  + ' >> ' + @workdir + @workfilename
      exec master..xp_cmdshell @cmd
     
      select @cmd = 'ftp -s:' + @workdir + @workfilename
     
      create table #a (id int identity(1,1), s varchar(1000))
      insert #a
      exec master..xp_cmdshell @cmd
     
      select id, ouputtmp = s from #a
go
 

STEP 4. Now in your Backup Job you need to add a second step that will execute the following t_SQL.
declare @path varchar(1000)
      declare @command varchar(666)
      set @path = 'c:\DB\' --the location of your local backup directory
      set @command = 'dir ' + @path + '* /B'
      print @command
 
      create table #tmp_dir_list (file_name_listing varchar(1000))
      insert into #tmp_dir_list
      exec master.dbo.xp_cmdshell @command
 
      -- clean up directory list:
      delete #tmp_dir_list where isnull(file_name_listing,'')=''
      delete #tmp_dir_list where isnull(file_name_listing,'')='File Not Found'
 
      select * from #tmp_dir_list
 
      -- for each file in list - rename
      declare @file_name varchar(555)
            , @new_file_name varchar(999)
 
      declare file_list cursor
            for select file_name_listing
                  from #tmp_dir_list
            for read only
     
      open file_list
 
      fetch next from file_list into @file_name
      while @@fetch_status=0
            begin
            exec s_ftp_PutFile     
            @FTPServer = '111.111.111.111' , --your ftp server
            @FTPUser = 'username' , -- ftp username
            @FTPPWD = 'passw0rd' , -- ftp password
            @FTPPath = '/' , - ftp directory
            @FTPFileName = @file_name ,
            @SourcePath = @path ,
            @SourceFile = @file_name,
            @workdir = 'c:\DB\' -- this is where we're going to record our log
            fetch next from file_list into @file_name
            end
      close file_list
      deallocate file_list
      drop table #tmp_dir_list
 
STEP 5. The last step should be executed on the success of a previous (which is an actual backup procedure).
Thats it!
Yaroslav Pentsarskyy
Blog: http://www.sharemuch.com/wordpress/