Compress SQL Server backups using WinZip - Part I

SQL Server Database Administrators often run out of space due to huge backup files that have been retained on the disk. This article illustrates how to take full and transaction log backups and compress the backup files using the reasonably priced WinZip.

Step 1

Install the WinZip software on the SQL Server machine. [Refer Fig 1.0]. WinZip has the command line add-on [Refer Fig 1.1] feature that you could download from their website www.WinZip.com. This command line add-on is available only when you purchase the software and register it.

Select the default options when installing WinZip and the command line add-on.

When you install the command line add-on of WinZip, it creates and copies the following file, used to compress the database backups. [Refer Fig 1.3]

Step 2

Create C:\ZIP.bat on the SQL Server machine with the following code. Refer Fig 1.4

"C:\PROGRAM FILES\WINZIP\WZZIP.EXE" -ee -ybc %1 %2

Step 3

Create the following procedure on the server. Download USP_BAKZIP.sql.

SET QUOTED_IDENTIFIER OFF
GO
USE MASTER
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[USP_BAKZIP]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [DBO].[USP_BAKZIP]
GO
CREATE PROCEDURE USP_BAKZIP
@DBNAME VARCHAR(256),
@BAKPATH VARCHAR(1000),
@ZIPPATH VARCHAR(1000),
@TYPE VARCHAR(1) -- F (FULL BACKUP) T (TRANSACTION LOG BACKUP)
AS
--CREATED BY : MAK
--CREATED DATE : OCT 12, 2005
--OBJECTIVE: TO BACKUP THE DATABASE OR TRANSACTIONAL LOG BACKUP AND ZIP IT USING WINZIP
DECLARE @SQLSTATEMENT VARCHAR(2000)
SET @SQLSTATEMENT =''
DECLARE @BTYPE VARCHAR(25)
DECLARE @BTYPEEXT VARCHAR(4)
DECLARE @TIMESTAM VARCHAR(20)
SET @TIMESTAM=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25),GETDATE(),120),'-','_'),':','_'),' ','_')
IF @TYPE ='F' 
BEGIN
SET @BTYPE =' DATABASE '
SET @BTYPEEXT='.BAK'
END
IF @TYPE ='T' 
BEGIN
SET @BTYPE =' LOG '
SET @BTYPEEXT='.TRN'
END
IF @TYPE NOT IN ('T','F') 
BEGIN
GOTO ERROR
END

SET @SQLSTATEMENT = 'BACKUP ' + @BTYPE + @DBNAME+' TO DISK ="'+@BAKPATH+@DBNAME+'_'+@TIMESTAM+@BTYPEEXT+'" '
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT @SQLSTATEMENT 
PRINT 'MESSAGE'
PRINT '-------'
EXEC (@SQLSTATEMENT)
SET @SQLSTATEMENT = 'C:\ZIP.BAT "'+@ZIPPATH+@DBNAME+'_'+@TIMESTAM+@BTYPEEXT+'.ZIP" "'+@BAKPATH+@DBNAME+'_'+@TIMESTAM+@BTYPEEXT+'"'
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT 
GOTO FINAL

ERROR:
PRINT 'SYNTAX : EXEC USP_BAKZIP "DATABASENAME","BAKPATH","ZIPPATH","TYPE"'
PRINT '"TYPE" SHOULD EITHER BE "F" FOR FULL BACKUP OR "T" FOR TRANSACTIONAL LOG BACKUP'
PRINT 'EXAMPLE: EXEC USP_BAKZIP "MASTER","D:\SQLDUMPS\","D:\SQLDUMPS\","F"'
GOTO FINAL
FINAL:

GO

Step 4

Let us execute the stored procedure as shown below:

EXEC USP_BAKZIP "MASTER","D:\SQLDUMPS\","D:\","F"

This statement does a full database backup and zips the backup file on to the location D:\.

Once the procedure is executed, the database is backed up and zipped with the following message.

SQL STATEMENT
-------------
BACKUP  DATABASE MASTER TO DISK ="D:\SQLDUMPS\MASTER_2005_10_12_15_59_27.BAK" 
MESSAGE
-------
Processed 1848 pages for database 'MASTER', file 'master' on file 1.
Processed 1 pages for database 'MASTER', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 0.288 seconds (52.568 MB/sec).
SQL STATEMENT
-------------
MESSAGE
-------
output
C:\WINDOWS\system32>"C:\PROGRAM FILES\WINZIP\WZZIP.EXE" -ee -ybc "D:\MASTER_2005_10_12_15_59_27.BAK.ZIP" "D:\SQLDUMPS\MASTER_2005_10_12_15_59_27.BAK" 
WinZip(R) Command Line Support Add-On Version 1.1 SR-1 (Build 6224)
Copyright (c) WinZip Computing, Inc. 1991-2004 - All Rights Reserved
Searching...
  Adding MASTER_2005_10_12_15_59_27.BAK...............................
creating Zip file D:\MASTER_2005_10_12_15_59_27.BAK.ZIP

(15 row(s) affected)

Let us execute the stored procedure as shown below.

EXEC USP_BAKZIP "Northwind","D:\SQLDUMPS\","D:\","T"

This statement would do transaction log backup and zip the backup file to the location D:\.

Once the procedure is executed the database transaction log would be backed up and zipped with the following message.

SQL STATEMENT
-------------
BACKUP  LOG northwind TO DISK ="D:\SQLDUMPS\northwind_2005_10_12_16_02_01.TRN" 
MESSAGE
-------
Processed 1 pages for database 'northwind', file 'Northwind_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.009 seconds (0.398 MB/sec).
SQL STATEMENT
-------------
MESSAGE
-------
output
C:\WINDOWS\system32>"C:\PROGRAM FILES\WINZIP\WZZIP.EXE" -ee -ybc "D:\northwind_2005_10_12_16_02_01.TRN.ZIP" "D:\SQLDUMPS\northwind_2005_10_12_16_02_01.TRN" 
WinZip(R) Command Line Support Add-On Version 1.1 SR-1 (Build 6224)
Copyright (c) WinZip Computing, Inc. 1991-2004 - All Rights Reserved
Searching...                                    ...         ..      
  Adding northwind_2005_10_12_16_02_01.TRN.....               .   
creating Zip file D:\northwind_2005_10_12_16_02_01.TRN.ZIP

(8 row(s) affected)

Conclusion

This article has illustrated how to take full backup and transaction log backups and compress the backup files using the reasonably priced WinZip. Part II of this article will illustrate how to delete the original backup file and how to uncompress the compressed backup files.

» See All Articles by Columnist MAK