Sample sql script to zip and transfer database backup file

Question

I was looking for a sample sql script to zip my database backup file (.bak) and transfer to a remote location. Please share if you have it with you.

Answer 1

You can use xp_cmdshell to invoke the commands for zipping and copying. In the sample here, I am using winzip command line (for zipping/unzipping) and xcopy for transferring files.

EXEC master..xp_cmdshell 'C:\"Program Files"\WinZip\wzzip C:\Database.bak.zip C:\Database.bak';
EXEC master..xp_cmdshell 'C:\"Program Files"\WinZip\wzunzip -o "C:\Database.bak.zip" "C:\Database"';
EXEC master..xp_cmdshell 'xcopy "C:\Database.bak.zip" "\\networkshare\Backups" /Y'

Answer 2

xp_cmdshell is one way, although it isn't ideal since enabling it makes the server less secure.

My open source project, SQL Server Compressed Backup, does what you are looking for in one step:

msbp.exe backup "db(database=model)" "zip64" "local(path=\\server\share\path\model.full.bak.zip)"

The installation of SQL Server Compressed Backup is just "xcopy" deployment -- there is nothing to install/uninstall, ideal if you just need to do this once.

It uses zip64 since the standard zip format has a 4 GB limit. Alternative compression formats available are gzip and bzip2 which have no limits.