SQLgzip.exe and SQLgzipInfo.exe

Simple compressed backup solution for SQL Server 2000, 2005, 2008

Backup compression has been introduced by Microsoft in SQL Server 2008 and in Enterprise edition only. It is better than nothing but it has following disadvantages: 

• cost - you have to pay for Enterprise Edition;
• can be used in SQL server 2008 Enterprise edition only (with restore on other editions); compression not available on other SQ server versions
• compression format is not compatible with widely used zip and gz formats, ie. compressed backup cannot be easily decompressed.

 In most cases we use compression (like gzip, zip, 7zip etc.) to save disk space and/or file transfer time for big databases. Using compression as a separate step in chain backup->compression->transfer->decompression->restore is time consuming and requires additional disk space.

The main "building bricks" - SQL backup/restore VDI and zlib library - are already available. Based on these "bricks" I developed my compressed backup utility (SQLgzip.exe) for SQL Server. Initially it has been developed for SQL server 2000, but it works for 2005 and 2008 versions as well ... as no "surprises" were introduced by Microsoft in VDI interface and backup file format.

SQLgzip.exe executed without any parameters displays short usage instructions:

Notation for BACKUP and RESTORE is almost the same as original - no need to learn new commands or notation. Just each parameter has to be taken in double quotas if it contains blanks (WITH options, for example). After successful BACKUP command execution "virtual device name" is updated with compressed backup file name (specified in command) in table "backupmediafamily": 

UPDATE msdb..backupmediafamily
SET physical_device_name=@wVdsName
WHERE physical_device_name=@fname 

New command EXTRACT has been introduced (instead of RESTORE) to avoid restore databases with name FILELISTONLY or INFOONLY in case of typo mistakes - no need in double quotas for this command.

There are three EXTRACT subcommands:

1. FILELISTONLY displays list of all files in compressed backup:

D:\SQLtools>sqlgzip extract filelistonly d:\temp2\model.bak.gz
Logical File Name                            Physical File Name
-----------------                            -------------------
modeldev                                     D:\MSSQL.1\MSSQL\DATA\model.mdf
modellog                                     D:\MSSQL.1\MSSQL\DATA\modellog.ldf

2. INFOONLY extracts some more useful information from compressed backup: 

D:\SQLtools>sqlgzip extract infoonly d:\temp2\model.bak.gz
Backup File:          d:\temp2\model.bak.gz
Backup Name:          Null
Backup Description:   Null
User Name:            MUCMSPDOM\admin1
SQL Server:           9.0.4053 - MUC39723
Backup Start Date:    2010-01-07 13:50:08
Database Name:        model
Creation Date:        2003-04-08 09:13:36
Comp. level/version:  90/611
SortOrder/CodePage:   52/0 

Logical File Name:    modeldev (size: 1245184)
Phisical File Name:   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf

Logical File Name:    modellog (size: 2621440)
Phisical File Name:   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf

3. BACKUPFILE simply decompress compressed backup to specified file:

D:\SQLtools>sqlgzip extract backupfile d:\temp2\model.bak.gz d:\temp
2\model_demo.bak
Performing restore from compressed backup "d:\temp2\model.bak.gz" to file "d:\temp2\model_demo.bak" ...
Successfully completed restore to file. 

All examples demonstrate utility usage from Command Prompt for simplicity. To use it in scheduled SQL Agent jobs you can use xp_cmdshell or sqlcmd.   

Command COPYONLY copies database without intermediate file on the same SQL server or between instances but on the same server (cluster). Example of usage you can see above. COPY_ONLY option is added internally for source database to avoid any impact on backup policy. Actually it is backup with implicit "WITH COPY_ONLY" for source database and restore with explicit "WITH"-options for destination database specified in COPYONLY command. Second server name (separated by semicolon) is needed copy to another instance.

Simple utility SQLgzipInfo.exe included in this package allows you to view backup info (the same as in LABELONLY, HEADERONLY and FILESONLY) without SQL server participation. It "understands" compressed and uncompressed backup files. Actually it is GUI version of "EXTRACT INFOONLY" command and its replacement in freeware version.

I implemented SQLgzip utility more than one year ago in company where I am working as SQL server administrator (AMADEUS Data Processing GmbH). We have about 70 SQL servers (production and testing, clustered and standalone) with different versions of SQL Server (2000, 2005 and 2008). SQLgzip utility is used to refresh test and reporting databases on different test and reporting servers on daily basis. Furthermore it allows us to save disk space for backups for business critical databases - for them compressed backups are stored for several days on disk. And most important - maintenance is more simple and faster then "traditional" backup-compress-decompress-restore approach.

July, 2010

Version of SQLgzip.exe utility 1.2.0 accepts list of database names for BACKUP command:

BACKUP dbname1,dbname2,...dbnameN X:\MSSQL\*\*_%d_%t.BAK.ZIP

or

BACKUP c:\temp\MY_SQLgzip.lst X:\MSSQL\*\*_%d_%t.BAK.ZIP

List of databases can be specified in 2 ways:

1. comma separated database names
2. in file which is ended with string "SQLgzip.lst" and where each database name is specified on separate line, like following:

master
msdb
model
etc.

Destination file name in such case can contain following placeholders:

   * - replaced by database name;
 %d - replaced by current date in format YYYYMMDD;
 %t - replaced by current time in format HHMMSS.

June, 2011

 
Last version SQLgzip v.1.2.3 (32- and 64-bit versions) with fixed ODBC driver selection (as per user request) can be downloaded here.

October, 2011