Monday, January 22, 2007

 

Backup and Restore tool for SQL Server with embedded compression and encryption

Backup & Restore tool for SQL Server with embedded compression and encryption

When I wrote Four cheap ways to get your SQL Server backup files smaller , I did it with SQL Server 7 and SQL Server 2000 in mind. Now, with SQL Server 2005 not supporting “Named Pipes” as target devices for backup/restore operations, only “three cheap ways to get SQL Server backup files smaller” would be available.

While I was revising the content of the referred post, I found myself developing a simple tool to perform compression/decompression while backing up or restoring and also adding the capability to encrypt/decrypt the result.

Finally, I got a tool I called ZipVDI with a short “readme.txt” file you can read bellow. ZipVDI is a very simple tool, not comparable to all those commercial tools making a good job in this area. All of them have a lot of interesting features, but there is an interesting one I am not able to find in those commercial tools: ZipVDI compression and encryption format is publicly available. Compression is done with gzip library and sample code showing how ZipVDI derives keys, IV, encrypts and decrypts is attached.
This means that any other tool (commercial or not) would be able to use or generate files in the “ZipVDI” format.

Why is this feature an important one? Let’s see: haven’t you see cases where different sites of the same company are not able to share SQL Server backups generated by these tools because they are using different tools in each site? Of course, this situation is much more common when the involved sites belong to different companies.
Of course, “ZipVDI” is not relevant enough to make others tools be aware of its file format, but if these commercial tools were able to interact each other, your company, your site or even your department would buy their favorite tool to use as widely as possible in its internal scope while still being able to directly interchange backup files with “others”.
Isn’t this the situation with commercial/free file compressors? When you buy a copy of WinZip, you expect to decompress almost any compressed file no matter the compression format.

If after reading the previous comment and the content of the “Readme.txt” file for ZipVDI tool, you still want to play with it, download a binary copy from
download ZipVDI


Readme.txt
License Agreement For ZipVDI
------------------------------


This program can be used to backup/restore SQL Server databases
to/from compressed and encrypted files.
If you encrypt a file with this program, and then forget your
password, there is no way you can get the file back
- and it is NOT MY FAULT!

If the program crashes whilst encrypting/decrypting and you
lose valuable data, then I apologise, but I am not to be
held responsible.


Limited Warranty:
-----------------

THIS SOFTWARE IS PROVIDED "AS IS" AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
THE POSSIBILITY OF SUCH DAMAGE.


Frequently Asked Questions
=============================

- What is ZipVDI?
- What is the compression algorithm used by ZipVDI?
- What are the encryption algorithms used by ZipVDI?
- What command line arguments can be used with ZipVDI?
- Is there deeper explanation for command line arguments?
- How do I convert a backup file generated with ZipVDI
to a regular SQL Server backup file?
- How do I convert a regular SQL Server backup file to
a file format readable by ZipVDI?


- What is ZipVDI?
===================


ZipVDI is a small console application that allows you to
execute backup/restore operations directly TO/FROM
compressed and encrypted files
.
Compression and encryption is done while the
backup/restore operation takes place, that is, they are
executed in memory without needing intermediate files.


- What is the compression algorithm used by ZipVDI?
===================================================


ZipVDI uses the zlib library to compress/decompress
the backup information.
Compression level ranges from 0 (no compression) to 9
(max compression), obviously inherited from the zlib
library

- What are the encryption algorithms used by ZipVDI?
====================================================


ZipVDI can encrypt/decrypt the backup information using:

- AES (rijndael) with a 256 bits Key and 128 bits IV
- Triple DES with 168 bits key and 128 bits IV

Key and IV are derived from a user pass phrase provided as
a command line argument.

However, due to Windows Crypto API provider limitations,
AES encryption can only be used on Windows 2003 and later
(Triple DES can be used in any of the currently supported
Windows versions)

- What command line arguments can be used with ZipVDI?
======================================================


ZipVDI uses getopts Command line argument parser from
Steve Mertz Its copyright notice
should be present among all the documentation of this tool.

By typing ZipVDI -h at the command prompt, a list of
available options and a short explanation is showed.

Usage: zipvdi [options]

--help, -h Displays this information
--instance, -i SQL Server instance name
default instance if omitted)
--sentence, -s SQL backup/restore sentence
use " to enclose the sentence)
--compress, -c Compression level (if omitted set
to 6)
--password, -p Pass phrase to encrypt/decrypt
use " to enclose the pass phrase).
If -p is present and pass phrase
is empty (you typed -p ""),
the program will prompt for it.
If -p is no present, no encryption
takes place.
--algorithm,-a Algorithm to encrypt: a(aes) d(3des)
(default algorithm is AES)
--logfile, -l File to store log information

Some samples:

* Backup pubs database to c:\pubs.bkp with the default
compression level and encrypting using AES with a
pass phrase

(next three lines are a single command)
zipvdi -s "backup database pubs to disk='c:\pubs.bkp'
with stats=1" -p "unbreakable pass phrase" -a a
-l "c:\pubs.bkp.log"

* Restore pubs_new database using the previous backup
and decrypting using AES with a pass phrase

(next five lines are a single command)
zipvdi -s "restore database pubs_new from disk = 'c:\pubs.bkp'
with move N'pubs' to N'c:\mssql\data\pubs_new.mdf',
move N'pubs_log' to N'c:\mssql\data\pubs_new_log.ldf',
replace, stats = 10" -p "unbreakable pass phrase"
-a a -l "c:\pubs_new.res.log"



- Is there deeper explanation for command line arguments?
=========================================================


Here, you can find deeper explanation for some command
line arguments

--instance, -i
--------------------------

When several SQL Server instances are running on the same
machine, use this argument to select which one is the
target instance for the backup/restore operation.

For example, if in addition to your default SQL Server
instance, you have a second instance named MySecondSQL,
you should execute the following command to backup the
pubs database hosted in the second instance

(next three lines are a single command)
zipvdi -i ServerName\MySecondSQL -s "backup database pubs
to disk='c:\pubs2.bkp'" -p "unbreakable pass phrase"
-a a -l "c:\pubs2.bkp.log"



--sentence, -s
-------------------------

This is the SQL sentence that performs the backup/restore
operation. Use double quotation marks to enclose the
sentence.

The same syntax supported by SQL Server should be used
with ZipVDI (you can use options such as CHECKSUM,
VERIFYONLY,...)with some restrictions:

- Only one backup/restore device can be specified.
Backing up/Restoring TO/FROM multiple devices
are not currently supported.

- Only disk can be specified as backup device
(TO DISK='xxx'/FROM DISK='xxx').
However, you can specify a named pipe or the
stdin/stdout as the file name used for the
backup/restore operation.

Example 1: backup pubs database to the named pipe
'\\.\pipe\PUBS_PIPE'
This possibility is very useful if you
want to send the output of this
program to the input of other tool
(like a different encryptor).
Note that in the example bellow,
no encryption takes place

(next three lines are a single command)
zipvdi -s "backup database pubs to
disk='\\.\pipe\PUBS_PIPE'"
-l "c:\pubs.bkp.log"


Example 2: backup pubs database to the stdout.
stdout is selected by choosing '-'
as the file name.
stdin can also be selected for restore operations

(next three lines are a single command)
zipvdi -s "backup database pubs to disk='-'"
-p "unbreakable pass phrase" -a a
-l "c:\pubs.bkp.log"


--compress, -c
-------------------------


This parameter defines the compression level that will be
used by zlib when compressing the backup content.
Valid values are from 0 (no compression) to 9 (max
compression). If not values is provided,
the default compression level of 6 will be used.



--algorithm, -a
--------------------------


This parameter defines which encryption/decryption will
be used. Backups can be encrypted with AES (a) or triple
DES (d). However, due to restrictions in the Windows Crypto
API system, AES encryption is only supported in
Windows 2003 or later.
Thus, if you want to share encrypted backups between
different Operating system, you should check all of them
support the selected encryption algorithm.

If this parameter is not present, then no
encryption/decryption is executed and the backup content
is only compressed/decompressed during the backup/restore
operation

--password, -p
-------------------------


Use this parameter to provide the pass phrase used to
derive the Key and the IV to be used with the encryption
algorithm.
Use double quotation marks to enclose pass phrase made of
more than one word.
If -p option is present but no pass phrase is entered
(you specify -p ""), then the program will ask for the pass
phrase before starting the operation.
This option is useful if you don't want to have the pass
phrase in your batch files, but requires you enter manually
the pass phrase at execution time

If the selected algorithm is AES, then the derived key will
have a length of 256 bits.
If the selected algorithm is 3 DES, then the derived key
will have a length of 168 bits.

--logfile, -l
-------------------------


Use this optional parameter to define a log file where
ZipVDI will store information about the operation: which
task is executed, target file, compression ratio, and any
message returned by the SQL Server during the
backup/restore operation (for example, if you specify the
clause "WITH STATS=10", the progress information will be
logged to this file).


- How do I convert a backup file generated with ZipVDI to
a regular SQL Server backup file?
==========================================================

ZipVDI backup operation is quite simple: the information
returned by the SQL Server as backup content is first
compressed using the zlib library and then it is encrypted
using either AES or Triple DES (if any of these algorithms
was selected).


+------------+ VDI +----------+ +---------+ +-------+
SQL Server -> Compress -> Encrypt -> Write
+------------+ +----------+ +---------+ +-------+


Thus, to convert the file generated by ZipVDI as the result
of a backup operation, you only need to revert both
operations:

- First you have to decrypt the file.
- then, you have to decompress the decrypted file

and you will get a backup file in the native SQL Server
backup file format.

To decrypt the ZipVDI generated file, you can use the
accompanying tool called DecryptBackup.
Also, the source code for this tool is provided.

To decompress the file obtained after decrypting,
you can use the gzip tool available at
www.gzip.org site.

These two steps could be done with one single tool (even
this functionality could be part of the ZipVDI tool).
The reason to create the tool DecryptBackup and opening
its code is to transparently show how ZipVDI encrypts.
ZipVDI is performing standard compression using a
"well know" compression algorithm and also is performing
standard encryption (using also "well know" encryption
algorithms) offering you the possibility to revert the
encryption by yourself since you can see the code to
encrypt.


- How do I convert a regular SQL Server backup file to a
file format readable by ZipVDI?
=========================================================


ZipVDI backup operation is quite simple: the information
returned by the SQL Server as backup content is first
compressed using the zlib library and then it is encrypted
using either AES or Triple DES (if any of these algorithms
was selected).
Thus, to convert a backup file generated by SQL Server to
a file that can be read by ZipVDI (and used to perform
restore operations) you only need to execute both
operations:

- First you have to compress the original backup
file using gzip tool.
- Then, you may encrypt the compressed file using
the accompanying tool called EncryptBackup.

And as explained in the previous question, these two steps
could be done with one single tool.
The reason to create the tool DecryptBackup and opening
its code is to transparently show how ZipVDI encrypts.
ZipVDI is performing standard compression using a
"well know" compression algorithm and also is performing
standard encryption (using also "well know" encryption
algorithms) offering you the possibility to revert the
encryption by yourself since you can see the code to
encrypt.


Comments:
That sounds interesting but it's a bit complicated for me.

From my experience I can recommend the tool I personally use for sql server backup.
It's LiteSpeed.

In comparison with native backups and some 3rd party tools that I had a chance to try, litespeed showed me the best results in speed, usability and features.
 
Interesting to know.
 
Good brief and this mail helped me alot in my college assignement. Thank you for your information.
 
thanks for this nice post 111213
 
thanks for this tips
 
Could you send me source files of zipvdi? Or give and explaination how do you use encryption on stream with block cipher like AES? I cant't figure out how to indicate last chunk and how to specify chunks of fixed size.
 
I have a problem when use zipvdi with sqlserver 2012. the problem is

VDS::Create fails: x80070005

any idea ???

Help me!!
 
Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?