Part I of this article illustrated how to use reasonably priced WinZip to do a full backup and transaction log backup. Part II of this article is going to illustrate how to delete the original backup file after zipping the backup file using WinZip and how to uncompress the compressed backup files using the WinZip command line wzunzip.exe.
Step 1
Create the following procedure on the server. Download USP_BAKZIP2.SQL.
SET QUOTED_IDENTIFIER OFF
GO
USE MASTER
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[DBO].[USP_BAKZIP2]')
AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [DBO].[USP_BAKZIP2]
GO
CREATE PROCEDURE USP_BAKZIP2
@DBNAME VARCHAR(256),
@BAKPATH VARCHAR(1000),
@ZIPPATH VARCHAR(1000),
@TYPE VARCHAR(1),
-- F (FULL BACKUP) T (TRANSACTION LOG BACKUP)
@DELETEFLAG BIT =1
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 @SQLSTATEMENT
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT
IF @DELETEFLAG=1
BEGIN
SET @SQLSTATEMENT = ' DEL '+@BAKPATH+@DBNAME+'_'+@TIMESTAM+@BTYPEEXT
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT @SQLSTATEMENT
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT
END
GOTO FINAL
ERROR:
PRINT 'SYNTAX : EXEC USP_BAKZIP2 "DATABASENAME","BAKPATH","ZIPPATH","TYPE",DELETEFLAG'
PRINT '"TYPE" SHOULD EITHER BE "F" FOR FULL BACKUP OR "T" FOR TRANSACTIONAL LOG BACKUP'
PRINT 'DELETEFLAG SHOULD EITHER BE 1 FOR DELETING THE BACKUP OR 0 FOR NOT TO DELETE THE BACKUP'
PRINT 'EXAMPLE: EXEC USP_BAKZIP2 "MASTER","D:\SQLDUMPS\","D:\SQLDUMPS\","F",1'
GOTO FINAL
FINAL:
GO
Step 2
Let us execute the stored procedure as shown below.
EXEC USP_BAKZIP2 "MASTER","D:\SQLDUMPS\","D:\SQLDUMPS\","F",1
This statement does a full database backup and zips the backup file to the location D:\SQLDumps. It then deletes the backup file that was created.
If the deleteflag =0, then the procedure will not delete the backup that was created.
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_18_14_55_51.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.341 seconds (44.398 MB/sec).
SQL STATEMENT
-------------
C:\ZIP.BAT "D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.BAK.ZIP" "D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.BAK"
MESSAGE
-------
output
C:\WINDOWS\system32>"C:\PROGRAM FILES\WINZIP\WZZIP.EXE" -ee -ybc
"D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.BAK.ZIP"
"D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.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_18_14_55_51.BAK............................... ..........
..................... .
creating Zip file D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.BAK.ZIP
(15 row(s) affected)
SQL STATEMENT
-------------
DEL D:\SQLDUMPS\MASTER_2005_10_18_14_55_51.BAK
MESSAGE
-------
output
(1 row(s) affected)
Let us execute the stored procedure as shown below.
EXEC USP_BAKZIP2 "Northwind","D:\SQLDUMPS\","D:\","T",1
This statement does a transaction log backup and zips the backup file to the location D:\. In addition, it deletes the transaction log file that is created.
Once the procedure is executed, the database transaction log is backed up and zipped with the following message.
SQL STATEMENT
-------------
BACKUP LOG Northwind TO DISK ="D:\SQLDUMPS\Northwind_2005_10_18_14_59_08.TRN"
MESSAGE
-------
BACKUP LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
SQL STATEMENT
-------------
C:\ZIP.BAT "D:\Northwind_2005_10_18_14_59_08.TRN.ZIP" "D:\SQLDUMPS\Northwind_2005_10_18_14_59_08.TRN"
MESSAGE
-------
output
C:\WINDOWS\system32>"C:\PROGRAM FILES\WINZIP\WZZIP.EXE" -ee -ybc
"D:\Northwind_2005_10_18_14_59_08.TRN.ZIP"
"D:\SQLDUMPS\Northwind_2005_10_18_14_59_08.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_18_14_59_08.TRN... .
creating Zip file D:\Northwind_2005_10_18_14_59_08.TRN.ZIP
(8 row(s) affected)
SQL STATEMENT
-------------
DEL D:\SQLDUMPS\Northwind_2005_10_18_14_59_08.TRN
MESSAGE
-------
output
(1 row(s) affected)
Now let us see how we can uncompress the compressed backup file using a stored procedure and WinZip.
Step 1
Create C:\unzip.bat on the SQL Server box as shown below. [Refer Fig 1.0]
"C:\PROGRAM FILES\WINZIP\wzunzip.exe" -ybc %1 %2
Fig 1.0
Step 2
Create the following procedure on the server. Download USP_UNZIP.SQL.
SET QUOTED_IDENTIFIER OFF
GO
USE MASTER
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[DBO].[USP_UNZIP]')
AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [DBO].[USP_UNZIP]
GO
CREATE PROCEDURE USP_UNZIP
@ZIPFILE VARCHAR(2000),
@BAKPATH VARCHAR(1000)
AS
--CREATED BY : MAK
--CREATED DATE : OCT 12, 2005
--OBJECTIVE: UNZIP THE GIVE ZIPPED FILE TO A FOLDER
--SYNTAX: USP_UNZIP "D:\Northwind_2005_10_18_14_59_08.TRN.ZIP","D:\"
DECLARE @SQLSTATEMENT VARCHAR(2000)
SET @SQLSTATEMENT =''
SET @SQLSTATEMENT = 'C:\UNZIP.BAT "'+@ZIPFILE+'" "'+@BAKPATH+'"'
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT @SQLSTATEMENT
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT
GO
Let us execute the stored procedure as shown below.
USP_UNZIP "D:\Northwind_2005_10_18_14_59_08.TRN.ZIP","D:\"
This statement un-compresses the backup file to the destination folder with the following message.
SQL STATEMENT
-------------
C:\UNZIP.BAT "D:\Northwind_2005_10_18_14_59_08.TRN.ZIP" "D:\"
MESSAGE
-------
output
C:\WINDOWS\system32>"C:\PROGRAM FILES\WINZIP\wzunzip.exe" -ybc "D:\Northwind_2005_10_18_14_59_08.TRN.ZIP" "D:\"
WinZip(R) Command Line Support Add-On Version 1.1 SR-1 (Build 6224)
Copyright (c) WinZip Computing, Inc. 1991-2004 - All Rights Reserved
Zip file: D:\Northwind_2005_10_18_14_59_08.TRN.ZIP
Searching...
unzipping D:\Northwind_2005_10_18_14_59_08.TRN.
(9 row(s) affected)
Conclusion
This article has illustrated how to delete the original backup file after zipping the backup files using WinZip. It also illustrated how to uncompress the compressed backup files using the WinZip command line wzunzip.exe.
» See All Articles by Columnist MAK
IE 7
Firefox 2.0
