Zip SQL Server database backup using command line by serverku

Long back we have seen some of the topics related SQL Server database backup copy to other local or external location using command line such as XOPY and FOR/F. Those posts show how to copy database backups copy to an external location. Now, as a part of the same process I want to add those backups to be ZIP. So let us continue for sample demo. Before ahead, it WinZip software with supports encryption and WinZip command line should be installed.

1.  Encryption and Zip all databases, backups located in a single directory. Here you just change a source and destination path for appropriate databases. Apply below code in BATCH file and schedule it.

SET SourceDirectory=F:\SourceDir\
SET DestDirectory=E:\DestDir\

FOR /F %%f in ('dir /b %VersionDirectory%*.bak') do  "c:\Program Files\WinZip\WzZip" -s"Password@123" -ycAES256 "%DestDirectory%%%~nf.zip" "%SourceDirectory%%%f"

It will encrypt database backups with AES256 method and created zip files to the destination directory.

2.  Encryption and Zip all database backups from multiple directories. Apply below code in BATCH file and schedule it.

SQLCMD -UZip -PZip@123 -S"TestServer" -dmsdb 
-Q"SET NOCOUNT ON; SELECT REPLACE(bmf.Physical_device_name,'.bak','.zip')  +','+ bmf.Physical_device_name FROM  
msdb.dbo.backupset (NOLOCK) bs INNER JOIN msdb.dbo.backupmediafamily (NOLOCK) bmf ON (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date > DATEADD(HOUR,-8,GETDATE()) AND BS.TYPE = 'D' AND bs.Database_name = 'ZipDB' ORDER BY bs.backup_finish_date DESC " 
-W -o "D:\Batchfiles\BackupZipFile.txt"

FOR /F "tokens=1,2 skip=2 delims=," %%G IN (D:\BatchFiles\BackupZipFile.txt) DO "C:\Program Files\WinZip\WzZip" -s"Password@123" -ycAES256 "%%G" "%%H"

Above command connects SQL Server using SQLCMD and read databases backup history for mentioning period and generate a text file for all backups rows with new lines. Then, using FOR /F code will create zip files for each database backup file with same name as the backup file name.

3.  Encrypt and Zip all databases backup from multiple directories for specific database only. Apply below code in BATCH file and schedule it with SQL Server agent job.

SQLCMD -UZip -PZip@123 -S"TestServer" -dmsdb 
-Q"SET NOCOUNT ON; SELECT TOP 1 REPLACE(bmf.Physical_device_name,'.bak','.zip')  +','+ bmf.Physical_device_name 
FROM  msdb.dbo.backupset (NOLOCK) bs INNER JOIN msdb.dbo.backupmediafamily (NOLOCK) bmf ON (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date > DATEADD(HOUR,-8,GETDATE()) AND BS.TYPE = 'D' AND bs.Database_name = '$(dbname)' ORDER BY bs.backup_finish_date DESC " 
-W -o "D:\Batchfiles\BackupZipFile_%1.txt" -vdbname = "%1"

FOR /F "tokens=1,2 skip=2 delims=," %%G IN (D:\BatchFiles\BackupZipFile_%1.txt) DO "c:\Program Files\WinZip\WzZip" -s"Password@123" -ycAES256 "%%G" "%%H"

Above batch file command is same as earlier with a small change of –v parameter which pass database name. “%1” is a parameter of database name value which pass from SQL server agent job where below code applied to job.

cmd.exe /c "D:\BatchFiles\BackupFilesToZip.bat" zipDB

This call batch file and pass database name ‘ZipDB’ to be zipped and encrypt then work same as described in earlier method 2. Text file output which generates in method 2 and 3.

-
D:\DBBackup\Full\ZipDB_20150417_223001243.zip,D:\DBBackup\Full\ZipDB_20150417_223001243.bak

Please make sure SQLCMD and FOR /F should be in single line individually without line breaks.
Here is just what I want to share for automated database backup encryption and ZIP using command line. Hope you enjoyed it and would like you to share the methods\process  you are using for database backup encryption and ZIP.