WHERE ID = OBJECT_ID(N’[DBO].[USP_BAKZIP2]’)
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.