Using SQL Server SQLCMD and WinZip wzzip to backup and zip/encrypt a database

Here's an example of how to create a BAT file to backup a SQL Server database using SQLCMD and to zip the backup file using WinZip wzzip with AES256 encryption.

 @ECHO OFF  
   
 set server=(local)  
 set database=Sandbox  
 set bakfile=C:\Users\matt\Desktop\temp\backup.bak  
 set zipfile=C:\Users\matt\Desktop\temp\backup.zip  
 set targetfile=C:\Users\matt\Desktop\temp\backup\backup.zip  
 set password=Password123  
   
 cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn  
 SQLCMD -E -S %server% -Q "BACKUP DATABASE %database% TO DISK = N'%bakfile%' WITH COPY_ONLY, NOFORMAT, INIT"  
   
 IF EXIST "%zipfile%" del /F "%zipfile%"  
   
 cd C:\Program Files\WinZip  
 wzzip "%zipfile%" "%bakfile%" -s%password% -ycAES256  
   
 IF EXIST "%bakfile%" del /F "%bakfile%"  
   
 move "%zipfile%" "%targetfile%"  

You could then use the BAT file to create a scheduled job to automate your backup process.

Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey