How to store database backup media

Most SQL Server backup recommendations tell you to always back up to disk first and then archive to tape for long-term storage. In general it is good practice to have at least a few days worth of backups on disk for quick restores, but having more than that doesn't usually get you much -- and it wastes a lot of disk space. After all, if there is a database failure, in most cases, you are going to restore to your most recent backup, so having extra backups on disk is not really required.

On the flip side, most databases do not store every single transaction in the database, whether they're for rolling back transactions or for auditing. You could do that with third-party tools, or you could create your own auditing mechanism, but that gets very expensive and harmful to your performance if you're logging every single database change that occurs. If you ever need to restore a dropped table or audit data, these long-term archived copies come in handy.

How many copies to keep

The best answer is everything. If you ever want to audit data at a particular point in time or recover lost data from a particular point in time, you would have to keep a copy of every full and transaction log backup. Again, it would be helpful to have a third-party auditing tool, such as Lumigent Technologies' Log Explorer, Coherent Software Corp.'s Log P.I., Apex SQL Tools' Apex SQL Log or Red Gate Software Ltd.'s SQL Log Rescue. If you are not using one, the best answer is to keep every single backup.

A lot of companies seem to recycle their tapes on a set rotation, so they can only establish what the database looked like at the end of the month. You could run full daily backups for the entire month and then save one complete backup for that month. In this scenario, the best you can do is restore one copy per month, leaving a lot of activity unaccounted for during that month.

If you are unable to archive every single backup for long-term storage, consider continuous data protection (CDP). With CDP tools, every single transaction is stored at a set interval so you have the ability to recover a database back to a specific point in time. They incrementally add changes, so everything is captured. Long term, the amount of data becomes quite large to manage, but this offers the flexibility to go back to any point in time. Some CDP applications include TimeSpring Software Corp.'s TimeData for SQL Server and XOSoft's Enterprise Rewinder.

How to store backups long term

Long-term archives, generally, are not needed often if ever, but they enable you to go back to a point in time if needed. The best solution for long-term storage is offsite in a secure location. Offsite facilities offer secure, controlled environments and a managed process for storage and retrieval. There are plenty of companies that offer this service if past company data is truly valuable; this is the best route to take for long-term storage.

When to encrypt backups

We have talked about encrypting backups in other tips. For offsite storage of backup media, you should definitely consider encrypting your backups. Once the tapes leave your place of business, they are supposed to remain in a secure location. But what happens if the tapes are lost or stolen? This is where encrypting the backup files comes into play. In the event your backups fall into the wrong hands, this additional step will safeguard your data.

The one downside to encrypting backup files is that you need an encryption key. More important, you have to remember the encryption key when you go to restore your backups. To help solve this problem you should store your encryption keys in a secure location separate from your backup tapes.

Third-party backup tools

If you are using third-party backup tools, you should include a copy of the software on the backup tapes. This way, if you ever need to restore a backup from several years ago, you will have the necessary software and version that was used to make the backup. In most cases, vendors try to ensure that future versions can read prior backup formats, but you can never be too sure. In addition, if you decide to change backup software at some point in time, you won't need to remember which software -- and which version -- was used to create the backup. Some backup tools include Red Gate's SQL Backup, Idera's SQLsafe and Quest Software Inc.'s LiteSpeed for SQL Server.

For the typical DBA, long-term backup storage is really not a concern. If the most recent backups are readily available on disk, 99% of the time this is where DBAs will go to recover a database. The long-term backups are usually more beneficial for auditing or in the event that data needs to be restored that has not been available on the system for quite some time. Having these long-term backups is beneficial because you never know if and when you may need one. The ideal situation would be to have every single database backup stored on tape, but, as I pointed out, this is often not the case. Talk to your backup administrators so you know what is being stored long term and what you can recover if ever asked.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

More information from SearchSQLServer.com