If It's Instant, It Isn't A Backup

Abstract

Because the snapshot isn't a full copy of the volume and at first contains no blocks, with just the new blocks being written, the snapshot isn't a full copy of the database. Because the snapshot isn't a full copy of the disks, the snapshot can't be trusted to be a valid backup.

Pretty much every storage vendor supports the concept of snapshots. Often these storage snapshots arc presented to companies by the vendor as a way to take SQL Server backups without having to worry about eating up large amounts of space. Some companies will even use this instead of tape backups. Because of the way these snapshots work, they don't make reliable backups.

Remember for a moment what causes a large majority of database corruption. It's a problem with the storage, in which the storage either incorrectly writes a block to the disk or a physical disk fails, causing the data to be lost. Snapshots use a technique called "copy on first write," in which, as blocks are written to for the first time by the disk array, the original block is copied to another location to be stored as part of the snapshot. Because the snapshot isn't a full copy of the volume and at first contains no blocks, with just the new blocks being written, the snapshot isn't a full copy of the database. Because the snapshot isn't a full copy of the disks, the snapshot can't be trusted to be a valid backup. If the source disks were to fail and the data on the production disk (where the database is stored) were lost, the snapshots would be useless because the blocks that haven't been written to yet would have been lost.

The same would apply if a block that hasn't been written to before, and therefore doesn't exist in the snapshot, were to become corrupt, cither by a cluster on the disk going bad or the array writing data incorrectly to the disks The block wouldn't be available for backups, and the backup wouldn't be valid.

This risk can be reduced, but not eliminated, by replicating the LUN that contains the storage we want to use snapshots for a backup as to another storage array, then taking the snapshots on that synchronously replicated array. However, this might not completely protect you. If the production array (the one that the SQL Server machine is talking to directly) were to write a bad block to the array, that bad write could be replicated to the remote site.

The next problem with using only snapshots for backup is that you have only a finite amount of space that can be used for backups, because SAN storage is very expensive. In the long run, tape is much cheaper for long-term storage because tapes can be sent offsite and kept effectively forever. Having the same data retention policy on active storage simply isn't a cost-effective solution for most people because eventually your snapshots will end up being the same size as your source disk. For a 100GB database, if you have snapshots that are taken daily and you keep 1 year's worth of snapshots, you'll end up having terabytes of space tied up in the snapshot backups, which could end up costing hundreds of thousands of dollars in disk space. Tapes, however, are very inexpensive and can hold hundreds of gigabytes per tape, which can then be sent offsite to another location (usually via an ofTsite storage company) for long-term safekeeping.