Compress backup file using T-SQL

Question

Is it possible to compress a backup file to .zip (or another) in a stored procedure invoked from a maintenance plan?

My maintenance plan contains these steps:

• check DB (consistency, re_index, maybe not important)

• full backup

• to check backup file by using restore_verifyonly

• shrink database

After restore_verifyonly/shrink database I want to compress this file.

• RoKa: What is your version of SQL?
• mKorbel: @RoKa SQL 2005/2008 R2 Standard, but for test purposes is possible on 2000 and 2012 too
• RoKa: SQL2008+ offers the ability to create a compressed backup which is approximately (very close at least) the same size as a zipped backup. Would that not be a viable solution rather than tinkering in a third party tool? @mKorbel
• mKorbel: @RoKa we are resticted (internally) to use shellexec e.g., then 3rd. party couldn't be executed from standard procedure, or am I wrong
• Aaron Bertrand: If you need to support 2000 and 2005 then you should consider not doing this from a procedure at all. RedGate's backup tool, for example, can manage all of your backups, including compression, and take the place of any maintenance plan. You can perform your checkdbs etc. separately.

Answer 1

Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK" 

You can then execute this cmd file from your 2000 / 2005 instances. You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.