Top three SQL Server database backup tips

Check out our top three solutions to the most vexing SQL Server database backup problems.

What you will learn in this tip: When it comes to backing up SQL Server databases, there are many considerations that must be taken into account beyond the usual things like tape capacity and backup windows. In this tip, learn about solutions for common SQL Server database backup problems.

Software compatibility needs to be addressed before you upgrade

A major SQL Server database backup problem is the issue of backup application compatibility. Some backup applications that work fine with SQL Server 2008 have major compatibility issues with SQL Server 2008 R2.

One such application is Symantec Corp. Backup Exec 12.5. If you attempt to back up a SQL Server 2008 R2 server, you will receive an error message stating that “An error was encountered while attempting to browse the contents of Microsoft SQL Server." If you are contemplating upgrading to SQL Server 2008 R2, it's crucial you ensure you backup software is compatible and can be used after the upgrade.

Be aware of the impact of backup compression

When Microsoft released SQL Server 2008, they introduced a new backup compression feature. At first the backup compression feature was only available in SQL Server 2008 Enterprise Edition, but Microsoft has made the feature available in the standard edition of SQL Server 2008 R2.

The effectiveness of backup compression varies, depending on the type of data contained in the SQL Server database, but I have seen compression rates in excess of 60%. In addition to shrinking the size of the backup, the backup compression feature also tends to reduce the backup duration by cutting down on the amount of disk I/O that's produced.

But the benefits of compression come at a price. Backup compression is extremely CPU intensive. In fact, it is not uncommon to see the baseline CPU usage increase by nearly 400% during a compressed backup. Keep in mind that this won’t necessarily be the case for all compressed backup jobs. The impact on the server’s CPU varies depending on the amount of data that can be compressed.

SQL Server tends to be a CPU-intensive application, so it is always important to weigh the benefits and the potential impact of any function that could potentially increase the server’s CPU usage. If SQL Server is running on a physical server with plenty of hardware resources, then a compressed backup may cause the server to slow down while the backup is in progress, but using compression doesn’t normally cause any catastrophic problems.

The same cannot necessarily be said for SQL Servers that are running inside of virtual machines. In a virtualized environment, multiple virtual machines share a physical server’s hardware resources. Ideally these resources should be shared in a way that prevents workload spikes on a virtual machine from impacting other virtual machines that are hosted on the same server.

The problem is that server virtualization has been sold as a way of reducing hardware costs. In an effort to maximize that savings, many IT departments over-commit hardware resources (especially CPU and memory) in an effort to reach the maximum possible virtual machine density per host server.  

Overcommitting CPU resources isn’t normally a problem as long as the CPU workload is predictable and proper capacity planning has been done to ensure that CPU cores are not overworked. However, if you suddenly introduce the heavy workloads associated with backup compression onto a virtualized SQL Server that is running on a host whose CPU resources have been overcommitted, the backup operation can adversely affect other virtual machines that are running on the host server.

Address database integrity to avoid database corruption

Although newer versions of SQL Server do a better job of preserving database integrity than some of the earlier versions did, integrity can sometimes be a problem in highly transactional environments. The bottom line is that database integrity needs to be addressed before a backup is made so that you don’t end up backing up corrupt data.

One way that you can help to ensure database integrity is by making use of the SQL Server 2008 R2 Maintenance Plan Wizard. The Maintenance Plan Wizard can be used to create SQL Server agent jobs that perform internal consistency checks within your databases to check for signs of corruption. The wizard can also be used to automate other tasks such as rebuilding indexes, removing empty database pages, updating index statistics, and running backup jobs.

SQL Server will attempt to automatically repair corruption whenever possible, but there may be instances in which corruption is simply too extensive for SQL to repair or there might be times in which you attempt to restore a backup only to discover that the backup contains corrupt data. In these types of situations you will most likely have to resort to using third-party utilities such as Office Recovery to repair the damage.

About the author: Brien M. Posey, MCSE, has previously received Microsoft's MVP award for Exchange Server, Windows Server and Internet Information Server (IIS). Brien has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. You can visit Brien's personal website at www.brienposey.com.

Dig Deeper on Backup and recovery software