SQL Server Backup Comparison

Although you can back up and restore SQL Server databases using SQL Server Management Studio (SSMS), the process isn't straightforward and doesn't scale well if you have many databases across many instances on many servers. Alternative backup solutions for SQL Server are available. Recently, I tested three of them:

These three products are specifically designed to back up SQL Server databases; they aren't general data-protection products that include SQL Server database backup functionality. Idera, maker of SQL safe backup, and NetApp, maker of Microsoft SQL Server Backup & Recovery, declined to participate in this review.

To test these three backup solutions, I set up a test environment that ran Windows Server 2008 R2 and SQL Server 2008 R2 with three instances deployed. Each instance hosted the AdventureWorks 2008 R2 sample database.

SQL Backup Pro 6.5

SQL Backup Pro 6.5 is suitable for DBAs who have a good working knowledge of backup and restore functionality. It's a powerful tool that doesn't provide the "hand holding" experience provided by products such as Acronis Recovery for MS SQL Server. DBAs who understand database backup and restore will appreciate how SQL Backup Pro's management console lets them leverage SQL Server's native backup and restore functionality without having to jump through some of the hoops presented by the native SQL Server management tools. Figure 1 shows SQL Backup Pro's management console.

Figure 1: SQL Backup Pro management console 

SQL Backup Pro supports databases hosted on SQL Server 2008 R2, SQL Server 2008, SQL Server 2005, and SQL Server 2000 SP3. The host OS can be running Windows 2000 SP4 or later.

As is the case with the native SQL Server backup functionality, SQL Backup Pro supports backup compression. It also supports clustered environments, because it's cluster-aware.

SQL Backup Pro is licensed per server and not per instance. You can back up databases from multiple instances with the same license as long as those instances are on the same host server.

SQL Backup Pro supports the following scenarios:

It is possible to restore a database from one instance to another, one server to another, using SQL Backup Pro (you must install one set of server components per SQL instance to do so). This feature is available on other products, too, such as Acronis Recovery for MS SQL Server and Microsoft System Center Data Protection Manager (DPM) 2012.

One feature that caught my eye was that prior to attempting to restore a database that used the full recovery model, the SQL Backup Pro interface prompted me to back up the tail of the transaction log. Although this is something that an experienced DBA should remember to do anyway, it's good that the application provided this reminder.

Acronis Recovery for MS SQL Server

As Figure 2 shows, Acronis Recovery for MS SQL Server provides a straightforward interface for backing up and restoring either some or all of the databases on a SQL Server instance. It supports all versions of SQL Server from SQL Server 7.0 SP4 through SQL Server 2008 R2. You can install Acronis Recovery on hosts running Windows 2000 SP4 or later. You need to deploy an agent to each server, but a single agent can be used to protect multiple instances installed on the same server. Acronis Recovery isn't cluster-aware.

Figure 2: Acronis Recovery management console 

With Acronis Recovery, you can:

Acronis Recovery includes a disaster recovery plan wizard. As with the product generally, this wizard is aimed at people who aren't experienced in thinking about disaster recovery issues. The wizard automates the process of creating a disaster recovery plan based on the characteristics of the database you want to protect.

You can back up to local devices, including tape libraries, autoloaders, and SCSI tape drives. You can also back up to configured FTP connections or existing Acronis Backup Servers on your network. When you configure a backup, you can choose between basic and advanced options. Using the basic options, you can configure a recovery point objective (RPO) of 10 minutes, an hour, or a day. When you configure a 10-minute schedule, a differential backup of the database is taken every 24 hours, with a transaction log backup taken every 10 minutes.

Depending on the state of the original database at the source location, when you perform a restore, you can restore to the point of failure, to the last backup point, to a specific point in time, or from a specific backup. Being able to restore to the point of failure is only possible if the full or bulk-logged recovery models are in use and an active transaction log is available. When you choose to configure a backup using the advanced options, you can configure items such as compression, commands to be executed prior to and after backup, validation, and backup throttling.

You can restore to the same computer, either in the original location or a different location. You can also restore to a different instance as long as the host server has the Acronis Recovery agent installed.

The product feels as though it's geared toward administrators who aren't necessarily experienced in the complexities of database options. Given that focus, it's odd that the product doesn't let you modify the recovery model of the database. Although changing a database recovery model is a relatively straightforward task using SSMS, so is configuring regular full, differential, and transaction log backups.

I experienced numerous problems when attempting to install Acronis Recovery in my test environment. My initial test environment included a domain-joined server running Server 2008 R2 SP1 with a default SQL Server 2008 R2 database engine instance. Although I was able to install the Acronis Recovery management console on the test server when logged on with a domain administrator account that was also configured as a SQL Server admin, I was unable to successfully deploy the Acronis Recovery agent from the management console using this account. When prompted for credentials, I provided both the domainusername and user principal name (UPN) formats, but the credentials were unrecognized. I then manually installed the agent, after which I was unable to use the management console to connect to the agent. I spent quite some time trying to resolve this problem before simply deploying a new standalone host. In the revised test environment, everything was configured using the default built-in administrator account, a situation that is unlikely to exist in real-world environments. Everything worked fine from that point on. I'm unable to determine whether the issue was with my test environment or some unusual snag with the product.

LiteSpeed for SQL Server 6.5

LiteSpeed for SQL Server 6.5 is a comprehensive enterprise-level SQL Server database backup and restore solution. As Figure 3 shows, its management console has the same look and feel as Microsoft Outlook and the Microsoft System Center 2012 suite. LiteSpeed supports SQL Server 7.0 through SQL Server 2012 running on Windows 2000 SP4 or later. LiteSpeed is cluster-aware for Windows Server 2003 and Server 2008 clusters.

Figure 3: LiteSpeed management console 

The solution includes the LiteSpeed Engine, which is a drop-in replacement for SQL Server's native backup and restore functionality. It provides its own compression and encryption functionality, which is substantially faster and more effective than the native SQL Server database engine functionality.

LiteSpeed has the following features:

LiteSpeed installation and configuration are straightforward. You can install the LiteSpeed Engine separately. It isn't a required component.

LiteSpeed is a sophisticated and mature product that lets you perform a broad array of SQL Server database backup and recovery tasks. The backup and restore wizards are helpful and find a good balance between requiring deeper technical knowledge and hand holding. My only nitpick was that when I performed a restore of an active database that used the full recovery model, LiteSpeed didn't automatically prompt me to back up the tail of the transaction log and threw an error when I attempted a recovery.

Editor's Choice

It's important to realize that these products specifically back up SQL Server databases. It's also important to realize that to perform a full recovery of a database, you need to regularly back up not only your user databases but also your system databases. Choosing between Acronis Recovery, LiteSpeed, and SQL Backup Pro will depend on your needs and license cost considerations. LiteSpeed is my editor's choice because of its comprehensive array of features and polished interface.