Script to Backup Zip and FTP SQL Server Database 

Thursday, September 22, 2011 4:48:34 PM

Here is a sql server script that makes compressed backups and moves them to an FTP. Quick and drity solution for the paranoic lone developer who's system just crashed...

There are only 2 preconditions: 7-zip installed and xp-cmdshell enabled (see this).

Well, 3 if you encounter issues with your firewall and want to replace the Windows FTP (no PASV mode) with something like NcFTP.

USE [master]
ALTER procedure [dbo].[spBackupAndFtpDB]
@FTPServer    varchar(128) ,
@FTPUser    varchar(128) ,
@FTPPWD        varchar(128) ,
@FTPPath    varchar(128) = '/' ,
@DBList            varchar(MAX),
@Delimiter    varchar(MAX) = ',',
@ZipBackup    bit = 1,
@DelteLocalFiles bit = 1,
@workdir    varchar(128) = 'C:\Windows\Temp\',
@7zipdir    varchar(255) = 'C:\Program Files\7-zip\'

as
    -- 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, '|', '^|'),'<','^<'),'>','^>')
    
    declare    @cmd varchar(1000)
    declare @workfilename varchar(128)
    
    DECLARE @SourceFile varchar(255)
    
    create table #a (id int identity(1,1), s varchar(1000))
           

    --start loop
    DECLARE @CSVString varchar(MAX);
    SET @CSVString = @DBList

    DECLARE @pos INT;
    DECLARE @DB VARCHAR(MAX);

    SELECT @pos = 1;

    WHILE @pos!= 0
    BEGIN
        SET @pos = CHARINDEX(@Delimiter,@CSVString);
        IF @pos != 0
            SET @DB = LTRIM(RTRIM(LEFT(@CSVString, @pos - 1)));
        ELSE
            SET @DB = LTRIM(RTRIM(@CSVString));

        IF( LEN(@DB) > 0)
        BEGIN

            --backup    
            SET @SourceFile = @DB + CONVERT(VARCHAR(6), GETDATE(), 12) + '.bak'
            SELECT @cmd = 'BACKUP DATABASE [' +   @DB + '] TO  DISK = N''' + @workdir + @SourceFile + ''' WITH NOFORMAT, INIT,  NAME = N''' + @DB + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'
            EXEC (@cmd)

            exec master..xp_cmdshell 'whoami'


            --compress
            IF @ZipBackup = 1 BEGIN
                BEGIN
                    --remove archives
                    SELECT @cmd = 'del "' + @workdir + '*.bak.7z"'
                    print @cmd
                    insert #a
                    exec master..xp_cmdshell @cmd
                END

                SELECT @cmd = '"' + @7zipdir +'7z" a ' + @workdir + @SourceFile + '.7z ' + @workdir + @SourceFile
                print @cmd
                insert #a
                exec master..xp_cmdshell @cmd
               
                IF @DelteLocalFiles = 1
                BEGIN
                    --remove backup
                    SELECT @cmd = 'del "' + @workdir + @SourceFile + '"'
                    print @cmd
                    insert #a
                    exec master..xp_cmdshell @cmd
                END
           
                --set zipped sourcefile
                SELECT @SourceFile = @SourceFile + '.7z'
            END

            --use NcFTP
            select @cmd = '"c:\program files (x86)\NcFTP Software\NcFTP\ncftpput.exe.lnk" -u ' + @FTPUser + ' -p ' + @FTPPWD + ' ' + @FTPServer + ' ' + @FTPPath + ' ' + @workdir + @SourceFile
            print @cmd
            insert #a
            exec master..xp_cmdshell @cmd
            
            
            ----use Windows FTP
            --select @workfilename = 'ftpcmd.txt'
           
            --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 ' + @workdir + @SourceFile + ' ' + @FTPPath + @SourceFile
            --        + ' >> ' + @workdir + @workfilename
            --exec master..xp_cmdshell @cmd
            --select    @cmd = 'echo '                    + 'quit'
            --        + ' >> ' + @workdir + @workfilename
            --exec master..xp_cmdshell @cmd
           
            --print @cmd
            --select @cmd = 'ftp -s:' + @workdir + @workfilename
           
            --insert #a
            --exec master..xp_cmdshell @cmd
           
--Not logged in.
--Login failed.
--File not found

            --IF @DelteLocalFiles = 1
            --BEGIN
            --    --remove backup
            --    SELECT @cmd = 'del "' + @workdir + @SourceFile + '"'
            --    print @cmd
            --    insert #a
            --    exec master..xp_cmdshell @cmd
            --END
           
           
            --report results
            SELECT id, ouputtmp = s FROM #a WHERE s IS NOT NULL
           
            IF EXISTS(SELECT * FROM #a WHERE s LIKE '%Not logged in%' OR s LIKE '%Login failed%' OR s LIKE '%File not found%' OR s like '%WARNINGS for files%')
            BEGIN
                SET @cmd = 'An Error occured during Backup and FTP. Details: '
                SELECT @cmd = @cmd + '; ' + ISNULL(s, '') FROM #a WHERE s IS NOT NULL
                RAISERROR (@cmd, -- Message text.
                   16, -- Severity.
                   1 -- State.
                   );
            END
           

        END

        SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
    END
    
    
    RETURN 0

 



Comments are closed on this post.

 
Site Map | Printable View | Design by creative & it consulting | © 2009 - 2014 Florian Morrenth