ARTICLES

Not too long ago I filmed the SQL Server 911 Deep Dive into SQL Server Backups. The workshop was focused on how to backup your SQL Server Database and how to restore that database. We spent an hour reviewing options to a full restore when something critical happens to your database. You can get the DVD’s of the backup workshop along with many other topics here in the learning center.

The majority of the SQL Server backup plans that I have done are focused on the backup of the database to disk.  The backups are then picked up by a tape library. In many cases, the steps in the SQL Server job that backup the database will conclude by executing the steps that will copy the backup file to the disk pool where the backup waits to be picked up by the tape library.

I prefer this method for a couple reasons, but mostly because the steps during a restore are shortened with steps removed. A few things to consider when backing up with this method are:

·         The person responsible for restoring the SQL Server backup in case of disaster is required to have knowledge in the tape library software. In many cases, a common familiarity with the software will not be enough to ensure a successful restore. With the large number of software options to backup to tape, this process could be very involved.

·         The alternate option is to have a specialist that focuses on the backup processes and software. This appears to be common where a backup administrator will have experience and skills to backup files from disk to tape while managing the backup retention periods. This issue with this option is the number of resources that are required to be involved with a database restore that requires you go to tape, for example when a hard drive goes bad.

When backing up to disk rather than backing up straight to tape, there is an increase on the amount of disk space that will need to be available to the server. However, the benefit to having a recent backup remain on disk rather than tape is the immediate access to that backup. When a disaster occurs, the database administrator can access the disk and the backup to begin the restore without having to utilize third party tape software or getting other resources involved as long as the disk remains online.

Backups can be run straight to tape and it was a popular option a number of versions ago. Many, if not most, of the backup to tape statements are still supported in recent versions of SQL Server. Many environments have used one of these two methods for years and they have proven to be reliable. The only real major changes between SQL Server 6.x and SQL Server 2000 was the addition of third party software that enables compression of the backup files and improves the speed of the backups.

With the release of SQL Server 2008, backup compression was added to SQL Server Enterprise Edition. In SQL Server 2008 R2, or Power Pivot, backup compression will even be included in the standard edition of SQL Server. There have been software packages that have been released that add a number of benefits to using compression software of one kind or another. The core of these methods can still focus on tape storage. With the reliability of disks and the cost of long term tape storage, one may ask the question if tape storage is in the future. Recently, I considered some options to the traditional backup methods.

The first method I reviewed I refer to as the mirror method. The mirror method in the simplest form is the copy of the backup files to a secondary location. SQL Server even supports mirrored backups with the command MIRROR TO.  The SQL Server version of the MIRROR TO, in a short summarized version, backs up to multiple locations. If you are looking for more information on how the MIRROR TO command works, you can review this in-depth information here. The method I reviewed used the same basic idea where a single backup file will be copied and duplicated on another disks.  In environments where multiple geographic locations are required by disaster recovery policies, a high traffic bandwidth option is used between the two storage areas.

The mirror method on disk can prove to be a strong option when looking for redundancy in your backup files. For the shops where a DR site is located in a different area, having the files needed in all locations can improve recovery speeds. The largest impact of this method is the cost. Backup plans with a long retention plan and large databases will find that the disk space needed grows quickly. This growth can be managed by third party compression tools or SQL Server compression tools however, the space needed is multiplied with the number of mirrors.

If you are backing up a database each night that has 100 gigs of information, consider the amount of disk space needed for a long term retention plan using the mirror method. This space can quickly overwhelm many budgets, however, in environments where the percentage of change is low, there could be a reasonable option. The de-dup method is the principal that a byte that has been backed up or moved to the backup location once does not need to be done again. It is possible to backup byte-level changes to the .mdf file directly. The same method could be used to backup the .bak files that SQL Server uses, however, the best way to do this is by using devices to backup your database. Devices can be added using the sp_adddumpdevice. This way the name of the file remains consistent and the de-dup software has a comparison file. De-Dup software features are used to the fullest after the first backup has been completed. Tthe data that has already been backed up will not be backed up again, much like a SQL Server differential backup. The de-dup software manages the information on the restore.

Some of the De-dup backup software will even connect directly to your SQL Server. The consideration when doing this is the transactional integrity. The de-dup software that I have reviewed to this point, with connections into SQL Server, use the SQL VSS Writer with Windows 2003 and newer. Some of these software solutions will work with Full and Simple recovery modes, however, the SQL VSS Writer does not support the backup of the transaction log. The concern here would be transactional integrity excluding databases where transactional integrity is not important and therefore, a de-dup backup method could be an efficient solution. 

The central idea to the Mirror Method and the De-Dup method is leaving the backup files on disk long term. One manages the disk so disk space is saved and the other does not take that into consideration. As the backup methods change, I consider how flexible I am of the change.   With backups, any new process needs to be tested in-depth.  I would hate to find that I have a problem on restore. Please take a second to vote on this article and send me any comments that you have.  It helps us decide on what topics we may write on next, and well, it makes us feel good to know if you liked it.