The goal of a good backup / recovery strategy is consistent data availability in the event of a corruption or disaster. So, while it’s helpful to determine how frequently you need full and differential backups, you definitely need to consider other factors. Database backups are only helpful if they can be restored. Also, where and how you store backups is important. What happens if your production database server goes down, or what if the hosting facility loses power in a flood or earthquake? Sure, not all scenarios apply to everyone, but storage, redundancy and consistency checks are all crucial in ensuring a successful backup / recovery strategy.
Disaster recovery strategies are outside the scope of this post, however leveraging the following SQL Server features can help strengthen your backup / recovery strategy and avoid integrity problems when you least expect them while maintaining data availability:
DBCC CHECKDB
You’ll almost certainly want to use DBCC CHECKDB to find corruptions before they become a problem. Paul Randal wrote CHECKDB for SQL Server 2005, and frequently posts in-depth, yet concise information on SQL Server internals and architecture on his blog. His series entitled CHECKDB From Every Angle tackles many of the topics you’ll need to consider and dispels the myth that you don’t have time for consistency checking.
BACKUP DATABASE…COPY_ONLY
If you’re performance tuning your production database or you have developers that need a copy of production data this option might help. You can create a full database backup without affecting the differential bitmap in the database. Consider this scenario: you perform full database backups every Monday, Wednesday, and Friday, and perform differential backups in between. Your development staff needs a full backup on Tuesday, but you know that doing so will affect the differential bitmap of your database. Enter the COPY_ONLY option. Using Copy-Only Backups lets you keep the developers happy and keep your backup strategy sound.
CREATE DATABASE…AS SNAPSHOT
Auditing production data is an industry in itself, but many shops rely on fast, ad-hoc reporting to satisfy management requests. These same shops often cannot afford to risk allowing ad-hoc statement executions for fear of data being accidentally modified. In these situations, database snapshots offer a good solution. A snapshot is a complete, read-only copy of a database that can be created extremely quickly and that consumes very little space on disk. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server writes the previous data to a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.
One final note – Quest’s Litespeed for SQL Server product’s low impact, high-performance compression technology can reduce storage costs (data can be compressed up to 95%) and SQL backup and recovery windows while maintaining complete control over the backup and recovery process. It even supports object-level recovery!! It’s definitely worth a look.