Introduction/ Issue:
Database snapshots and backups are often mistaken for the same feature because both capture the state of a database at a specific point in time. However, they serve very different purposes. Relying on a database snapshot as a backup can lead to permanent data loss if the source database or storage fails. Understanding the difference is essential for every SQL Server DBA.
Why we need to do / Cause of the issue:
A common misconception is that a database snapshot provides the same level of protection as a backup. In reality, a snapshot depends on the source database and resides on the same SQL Server instance. If the source database becomes unavailable due to hardware failure, storage corruption, or accidental deletion, the snapshot is also lost.
This misunderstanding can result in:
Inability to recover data after a server failure.
Loss of critical production databases.
Longer recovery times during incidents.
Incorrect disaster recovery planning.
Knowing when to use a snapshot and when to perform a backup helps ensure both quick recovery and long-term data protection.
How do we solve:
The solution is to understand the purpose of each feature and use them appropriately.
Database Snapshot
A database snapshot is a read-only, point-in-time view of a database. It uses a copy-on-write mechanism, where only modified data pages are stored after the snapshot is created. Since it depends on the source database, it cannot be used as a disaster recovery solution.
Example: Creating a Snapshot
CREATE DATABASE AdventureWorks_Snapshot
ON
(
NAME = AdventureWorks_data,
FILENAME = ‘C:\Snapshots\AW_Snap.ss’
)
AS SNAPSHOT OF AdventureWorks;
A snapshot is useful for:
Performing schema changes.
Testing deployments.
Quick rollback before risky operations.
Read-only reporting.
Database Backup
A backup creates an independent copy of the database that can be restored even if the original database or server is lost.
SQL Server supports:
Full Backup – Complete database backup.
Differential Backup – Captures changes since the last full backup.
Transaction Log Backup – Enables point-in-time recovery in Full or Bulk-Logged recovery models.
Example: Full Backup
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\Backups\AdventureWorks_Full.bak’
WITH COMPRESSION, CHECKSUM;
Backups are recommended for:
Disaster recovery.
Long-term data protection.
Server migration.
Restoring databases to another SQL Server instance.
Real-Time Scenario:
Before deploying a production schema change, a DBA creates a database snapshot to provide a quick rollback option if the deployment fails. At the same time, a full database backup is taken to protect against unexpected server or storage failures.
If the deployment introduces issues, the database can be quickly reverted using the snapshot. If the server experiences a hardware failure, the database can still be restored using the backup. Using both together provides fast recovery as well as complete disaster protection.
Conclusion:
Database snapshots and backups are designed for different purposes. A snapshot is a fast, read-only recovery point for short-term operations, while a backup is an independent copy used for long-term protection and disaster recovery.
A snapshot should never replace a backup. The best practice is to use snapshots before high-risk changes and maintain a proper backup strategy with full, differential, and transaction log backups to ensure complete data protection.
Recent Posts