Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Backup 3.0

By Kathi Kellenberger,

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.

Ease of Use 4.5 Very easy to use.
Feature Set 4 Just about everything I expected with some nice extras. Missing is a way to use the GUI remotely.
Value 5 Can I give it a 6?
Technical Support 4 Lots of options are available. The technical support person was very pleasant.
Lack of Bugs 3.8 I really didn't find any bugs, but they need to provide more helpful error messages when things go wrong.
Documentation 5 Helpful and nicely organized.
Performance 5 You should see the same or better performance than SQL native backup.
Installation 5 Quick and easy.
Learning Curve 5 Easy to use. Enhanced features so less scripting is required.
Overall 4.5 A feature-rich, easy to use, and inexpensive backup solution!

Product Information

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

Developer: Red-Gate Software
Pricing:

LicensesPrice*Price with 12 months
support & upgrades
1 $295.00 $369.00
5 $890.00 $1,113.00
10 $1,590.00 $1,988.00
*All pricing in US dollars.

Total article views: 12364 | Views in the last 30 days: 6
 
Related Articles
FORUM

Production Server DB backup Restored on Development Server Backup

Production Server DB backup Restored on Development Server Backup

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

FORUM

Restore multiple Differential backups

Restore multiple Differential backups

FORUM
FORUM

Restore with no backup

Database dropped, no backup. Need to restore

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones