Introduction

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.

Environment

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.

Installation

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:

• Welcome screen.
• Accept the license agreement.
• Provide a location for the program.
• Check if the extended stored procedures should be installed on the default

  instance.

• Install.
• Finish.

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

complete.

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

command.

Sample commands

• 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 =

  'F:\Backups\Pubs\Logs\*.*' "'

• Automatically name your files:

  master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK =

  ''e:\backups\<AUTO>''"'

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

Support

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.

Conclusions

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

at once.

Ratings

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.

Product Information

Web Site: http://www.red-gate.com/sql/sql_backup.htm

Developer: Red-Gate Software

Pricing:

*All pricing in US dollars.