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.