5

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.

Improve this question
  • What is your version of SQL? – RoKa Apr 19 '13 at 11:07
  • @RoKa SQL 2005/2008 R2 Standard, but for test purposes is possible on 2000 and 2012 too – mKorbel Apr 19 '13 at 11:26
  • 3
    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 – RoKa Apr 19 '13 at 11:39
  • @RoKa we are resticted (internally) to use shellexec e.g., then 3rd. party couldn't be executed from standard procedure, or am I wrong – mKorbel Apr 19 '13 at 11:48
  • 2
    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. – Aaron Bertrand Apr 19 '13 at 12:49
6

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.