Sample sql script for zip and transfer database backup file

Question

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

Answer 1

You can use xp_cmdshell to invoke commands to copy and copy. In the example here, I use the winzip command line (for zipping / unzipping) and xcopy to transfer 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 is not ideal, since it makes the server less secure.

, SQL Server, , :

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

SQL Server - "xcopy" - /, , .

zip64, zip 4 . : gzip bzip2, .

Answer 3

Why not use simple tools like SqlBackupAndFtp ? They do exactly what you need (sql backup + move to remote location) using a simple interface, and you do not need to write any scripts.