Zip Backups and Copy to Secondary (free)

/*
--********************************************************
-- 
-- 
-- Designed to ship backup files from primary log-shipping server to secondary log
-- shipping server for DR reasons.
--
-- Date Who What
-- ----------    ----------- -----------------------------------------------
-- 12/30/2008 Jeff Bennett Initial 
-- 1/09/2009 Jeff Bennett Genericized

stored procedure is named DBA.dbo.usp_compress_and_send
input parameter can be either 'A' for 'ALL', 'U' for 'USER', or 'D' for 'DR'
input parameter defaults to 'A'
procedure compresses ALL backups and only ships those that meet criteria of input parameter.
Testing History.....


*/CREATE PROCEDURE dbo.usp_compress_and_send
@dbtype char(1) = 'A'
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON

-- ******************************
-- initial variable declaration
-- ******************************
 --input variable can be 'A' for'ALL', 'U' for 'USER', or 'D' for 'DR'
 SET @dbtype = UPPER(@dbtype)
 IF (@dbtype not in ('A','U','D')) OR (@dbtype IS NULL) 
    BEGIN
    SET @dbtype = 'A' -- Default value
    END

-- *************************************************
-- find default backup directory location on primary
-- *************************************************
DECLARE @rc int, @dir nvarchar(4000)
SET @dir = ''
EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir output, 'no_output'

-- *********************************************************************************************
-- This command will zip up ALL *.BAK files in directory specified and directories below this
-- *********************************************************************************************
DECLARE @forfilezipcommand    varchar(255)
DECLARE @lastbackupdate varchar(10)
SET @lastbackupdate = convert(varchar(10),getdate()- 1,101)

--****************************************************
-- this command will compress all files and give them .zip extension, while retaining original file names
--****************************************************
SET @forfilezipcommand = 'FORFILES /p ' + @dir + ' /m *.bak /d ' + @lastbackupdate + ' /c "D:\gzip.exe -N -S.zip *.BAK"'
EXEC xp_cmdshell @forfilezipcommand 

-- ***********************************************
-- Determine where to copy files to on remote server (used log-shipping tables)
-- our backup directories do not retain instance name format, so I strip these out below
-- ***********************************************
DECLARE @secondserver varchar(30)
SELECT * INTO #lsps FROM msdb..log_shipping_primary_secondaries
IF @@ROWCOUNT <> 0
 BEGIN
     SELECT @secondserver = '\\' + secondary_server FROM #lsps
    -- PRINT @secondserver
     SET @secondserver = REPLACE(@secondserver, '\instancename1','')
     SET @secondserver = REPLACE(@secondserver,'\anyinstancename2','')
     SET @secondserver = REPLACE(@secondserver,'\someotherinstancename','')
 END
ELSE
 BEGIN     
    RAISERROR('ZIP and SHIP process requires a secondary server to send backup files',16,1)
 END
DROP TABLE #lsps

CREATE TABLE #DBS (DBName varchar(35),UserDB bit , Logshipped bit)
INSERT INTO #DBS SELECT name, 0, 0 FROM master..sysdatabases
UPDATE #DBS SET UserDB = 1 where DBName not in ('master', 'msdb','tempdb','model','DBA')
UPDATE #DBS SET Logshipped = 1 where DBName in (select secondary_database from msdb..log_shipping_primary_secondaries)

DECLARE @copycommand varchar(255)
DECLARE @dbfilename varchar(30)
DECLARE @userdb bit
DECLARE @logshipped bit

DECLARE filecursor CURSOR FOR
    SELECT DBName, UserDB, Logshipped FROM #DBS

OPEN filecursor

-- ***********************************************
-- Determine which files to copy based on input var
-- ***********************************************

FETCH NEXT FROM filecursor INTO @dbfilename, @userdb, @logshipped
WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 IF ((@dbtype is NULL) or (@dbtype = 'A'))
        BEGIN
            -- set logic to create copy statement and execute entire set of files
            SET @copycommand = 'COPY /Y /B ' + @dir + '\' + @dbfilename + '*.zip ' + @secondserver + '\' + REPLACE(@dir,':','$')
--            PRINT @copycommand
            EXEC xp_cmdshell @copycommand
     END 
    IF @dbtype = 'U' AND @userdb = 1
        BEGIN
            -- set logic to create copy statements only for user defined databases
            SET @copycommand = 'COPY /Y /B ' + @dir + '\' + @dbfilename + '*.zip ' + @secondserver + '\' + REPLACE(@dir,':','$') 
--            PRINT @copycommand
            EXEC xp_cmdshell @copycommand
        END
    IF @dbtype = 'D' AND @logshipped = 1
        BEGIN
            -- set logic to create copy statements only for logshipped databases
            SET @copycommand = 'COPY /Y /B ' + @dir + '\' + @dbfilename + '*.zip ' + @secondserver + '\' + REPLACE(@dir,':','$') 
--            PRINT @copycommand
            EXEC xp_cmdshell @copycommand
        END
    FETCH NEXT FROM filecursor INTO @dbfilename, @userdb, @logshipped
 END

-- *******************************************
-- Deallocate and drop tables no longer needed
-- *******************************************
CLOSE filecursor
DEALLOCATE filecursor
DROP TABLE #DBS

END