Recently I spent a few days working with a new product, SQLZip. In a nutshell
it's an add in for SQL that let's you do compressed backups. Interesting? Drive
space is cheap is these days, right? Well, if you're adding an IDE drive to your
workstation it's cheap. Buy a good size SCSI drive and it's still more than
lunch money. Sometimes it's not a matter of buying the drive, you have to buy
another container to hold them!
It's a small download, less than 1 meg. The install is a little unusual, it
creates a folder and puts files under Program Files but doesn't actually alter
your SQL installation. Once you've got that done there is a batch file that runs
a script using OSQL. I'd rather see this handled by the installation app itself
but at least this way you get a chance to see what is going on. It creates on
extended procedure and six support procedures. The documentation states it
supports named instances but due to time constraints I did not test it. I had
absolutely no problem following the installation instructions, no errors
occurred. A good start!
Here is a sample backup and restore script (right from the docs):
| execute sp_sqlzip_backup_mt @dbname='pubs' ,@reptfile='d:\mssql\backup\pubs.backup.4parts.report' ,@parts=4 ,@bkupfile1='d:\mssql\backup\pubs1.zbak' ,@bkupfile2='d:\mssql\backup\pubs2.zbak' ,@bkupfile3='d:\mssql\backup\pubs3.zbak' ,@bkupfile4='d:\mssql\backup\pubs4.zbak' execute sp_sqlzip_restore_mt @dbname='pubs' ,@reptfile='d:\mssql\backup\pubs.restore.report' ,@parts=4 ,@bkupfile1='d:\mssql\backup\pubs1.zbak' ,@bkupfile2='d:\mssql\backup\pubs2.zbak' ,@bkupfile3='d:\mssql\backup\pubs3.zbak' ,@bkupfile4='d:\mssql\backup\pubs4.zbak' |
Nervous about using a third party backup? This utility interacts with the SQL
backup process and compresses the file on the fly as it writes the file to disk.
I did a full native backup on my current Pubs database, ended up with about a 1
meg .bak file. I did the same backup with SQLZip, resulting file was just over
300k. As you can see in the sample code above it supports multi file backup and
is multi processor aware. Another nice feature is an included script that will
decompress the zbak (proprietary format) file to a standard SQL bak file. I also
sent several emails to tech support with questions and got timely responses
back.
Downside? In the current version you can do backup and restore using TSQL
only, there is no COM or DMO interface and you can't use it from Enterprise
Manager (though the native backup and restore continues to work). That's
really the only thing I can see, especially if you depend on maintenance plans
for your backups.
Pricing starts at $500 for a single server, discounts for multiple licenses
are available. I'm going to continue testing this for a while, but if it
continues to perform well I think this will get added to my wish list! Give it a
try, I think you'll find it to be useful.
