Managing backups, restores and backup files are important aspects of a DBA's
job. SQL Server provides easy to use utilities to perform these tasks. Whether
you choose to use Enterprise Manager, the Maintenance Wizard, scripting or DMO,
you already have some great tools at your disposal. Many DBAs, and I am among
them, prefer to backup databases to disk. Then, later, the nightly tape backups
will copy up the backup files and the tapes are sent off-site.
Backup tapes are expensive and large backup files require more tapes.
Legislation affecting security requirements of data such as HIPAA and Sarbanes
Oxley demand tighter control over backup files. SQL Server 2000 doesn't provide
a way to encrypt backups. Try this just for fun. Backup a small database like
pubs. Then open the backup file in Notepad. Scroll down halfway. You'll be
shocked at what you can see!
Red Gate has introduced a new product called SQL Backup that will ease these
challenges. For a very reasonable price, you can use this software to decrease
the backup file sizes and the amount of time it takes to backup your databases.
You can also encrypt and password protect the backups if that is required. Read
on to find out more about this exceptional product.
I installed SQL Backup on a Windows 2000 SP4 server with SQL 2000 SP3. The
product works only with SQL 2000 at this time. It is also compatible with
Windows XP and 2003.
Vendor Comment: The .NET Framework is not required.
The installation process is very simple. I quickly downloaded the 5 MB install
file from Red Gate's website and started the wizard. The installation wizard
has just a few screens and not many options:
Accept the license agreement.
Provide a location for the program.
Check if the extended stored procedures should be installed on the default
Figure 1: Install the extended stored procedures.
You can install the extended stored procedures on the default or other
instances later through the SQL Backup program.
Using SQL Backup
Red Gate has done a terrific job of making SQL Backup simple and easy to use.
There are three methods available to use the product: through the wizards
provided with the GUI, command line, and extended stored procedures. In order
to use the GUI you have to log on to the server locally or through some form of
remote control. I used Terminal Services with no problems. While most of
the use of this product will probably be as part of maintenance plans using the
extended stored procedures, I think the product would benefit by having a
client-only install with the ability to connect to multiple servers.
A Client-only install and utility is under development.
The GUI provided more information than I expected. You can view sp_who2, a SQL
Backup activity log, and backup information from MSDE for each database.
Figure 2: The SQL Backup GUI
The help provided with the product is excellent! It contains step-by-step
instructions for using the wizards and for setting up a job that will remove
the files after a specific number of days. You can indicate a format for
automatically naming the backup files including a date stamp. This product even
beats the Maintenance Wizard by featuring an option to delete expired backup
files before the new backups are created. For those of you who would
like to use log shipping, instructions are provided.
You can do just about anything with this product that you can do with native
SQL backups. Three options are not available: cumulative backups in the same
file, backups/restores of single files or file-groups, and it doesn't work on
64-bit platforms. Of course there are additional features, like the compression
levels and the password for encryption. The higher the compression level, the
longer it takes to backup and restore. If faster restores are critical you may
want to choose a lower compression level. Either way, you will save disk
space. A command line tool to convert SQL Backup files to native SQL
backup files is provided and may be distributed freely. Another extra is
the ability to email the results using SMTP mail. This is set up under
"Options". Just when I think I have found all of the goodies, I discover more.
The Backup Wizard allows you to save the settings that can be used to simplify
the scripting of subsequent backup jobs.
Figure 3: Extras
Once you get through the steps of the Backup Wizard, scripts are available that
you can use to set up your maintenance plan. A nice feature of the Backup Wizard
is that it reports the progress of the backup and provides a summary once it is
Figure 4: Summary screen
One of my favorite features is the ability to define a backup file format. You
can then replace the file name with "<AUTO>" in your backup command.
I backed up a database with 23.2 GB of data in 13 minutes. The backup file
using Red Gate's SQL Backup with level 1 compression (fastest) was 4.9 GB, a
79% reduction in size. Backing up the same database using SQL native backup
took 36 minutes. The native restore took a whopping 101 minutes. The Red Gate
restore took only 25 minutes.
The software uses the native SQL command plus the SQL Backup extensions as a
parameter to the extended stored procedure or command line utility. This makes
it easy to translate existing commands to the SQL Backup syntax. They thought
of lots of things that allow you to avoid writing elaborate scripts. A very
cool feature is a way to restore all of the log files in a folder with one
Backup a database with encryption:
master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK = ''e:\backups\pubs.sqb''
WITH PASSWORD = ''mykey'' " '
Apply all logs in a directory:
master..sqlbackup '-SQL "RESTORE LOG pubs FROM DISK =
Automatically name your files:
master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK =
Even though there is a table of error messages provided, the error messages
that I received when things went wrong were not helpful. When trying to backup
to a missing directory, I received error 620 stating just "Error writing to
backup file(s)". When I tried to restore to a database with open connections, I
received error 1010. In another case, a scheduled job with a syntax error did
not cause the job to report failure, so make sure you test your scripts.
Figure 5: Error message
The website contains an FAQ section and a support forum that Red-Gate technical
support does monitor. I really didn't have any technical questions to ask since
the product worked so well for me, but the support person that I talked to was
friendly and helpful. I asked if they were were working on a version that would
be compatible with SQL 2005. He assured me that they were, and that it will be
available as soon as SQL 2005 is available. Because their offices are located
in the UK, you may have to negotiate time differences if you work in another
part of the world. He suggested that questions be posted to the forum, but that
they are also very happy to help you by phone.
Overall, I was very impressed with SQL Backup. I appreciated the extra features,
like removing old backup files, naming files automatically with a date-time
stamp, the ability to restore all log backup files in a folder with one
command, and an option to move backup files after they are restored. I didn't
like the error messages I received when things went wrong.
If you are in need of help with your backups, I suggest you give Red Gate's SQL
Backup a try. Download a 14-day free trial and see how much space it can
save you. When you decide to purchase SQL Backup, you will find that the
pricing is very attractive with a large discount when you purchase 10 licenses
I will rate each of the following using a scale from 1 to 5. 5 being the best
and 1 being the worst. Comments are in the last column.
|Ease of Use
||Very easy to use.
||Just about everything I expected with some nice extras. Missing is a way to use
the GUI remotely.
||Can I give it a 6?
||Lots of options are available. The technical support person was very pleasant.
|Lack of Bugs
||I really didn't find any bugs, but they need to provide more helpful error
messages when things go wrong.
||Helpful and nicely organized.
||You should see the same or better performance than SQL native backup.
||Quick and easy.
||Easy to use. Enhanced features so less scripting is required.
||A feature-rich, easy to use, and inexpensive backup solution!
Web Site: http://www.red-gate.com/sql/sql_backup.htm
Developer: Red-Gate Software
Licenses||Price*||Price with 12 months|
*All pricing in US dollars.