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:
- Red Gate Software's SQL Backup Pro 6.5
- Acronis Recovery for MS SQL Server
- Quest Software's LiteSpeed for SQL Server 6.5
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:
- Backup of multiple databases and transaction logs
- Restoration of databases to the original location, a different location, or a new database; all restore options supported by T-SQL are supported by SQL Backup Pro, including restoring the transaction log to a particular point in time for databases that use the appropriate recovery model
- Scheduled transaction log shipping to instances on which the SQL Backup Pro agent is installed
- Command-line backup and restore
- Individual object recovery with SQL Object Level Recovery Pro
- Full support for FILESTREAM storage
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.
|
SQL Backup Pro 6.5 |
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:
- Back up and restore more than one database on an instance simultaneously
- Automate recovery to the point of failure
- Perform backups of remote machines using integrated FTP clients
- Use the Acronis Recovery SDK to create custom solutions that integrate into existing SQL Server applications
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 domain\username 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.
|
Acronis Recovery for MS SQL Server |
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:
- Support for full, differential, transaction log, and fast compression backups of databases or specific files and file groups
- Support for backing up to disks, tapes, IBM Tivoli Storage Manager (TSM), and TSM Archive
- The ability to create Double-Click Restore executables, which let you restore backups on servers that don't have LiteSpeed installed
- Backup templates, which let you configure items such as the backup strategy, the backup location, which databases should be backed up, whether transaction logs should be backed up, the backup schedule, and the retention policies; these templates can automatically be deployed to other instances as either maintenance plans or SQL Server Agent jobs
- Support for object, data, and transaction-level recovery
- The ability to run SELECT queries directly against backups; you can even join tables from active databases and backups
- Transaction Log Reader, which lets you undo and redo transactions
- Support for CPU affinity
- Support for log shipping
- Full command-line support
- Comprehensive tools to analyze the effectiveness of backups
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.
|
LiteSpeed for SQL Server 6.5 |
