SQL Snapshot vs Backup – When to choose snapshot over backup

SQL Server backup is my default backup strategy in all situations until yesterday, I never thought of snapshot backups which is definitely a wrong choice.
In this blog, I would like to share when I will use snapshot backup in future!

Backup:
During a typical Full backup, all the pages of database are copied to a different location and this backup can be moved to a different Server, SAN, Tape, any other media. This can be used even after decades if required.
Back strategy can be designed and scheduled even for a point in time recovery of any time frame using Full, Differential, and transactional backup. This is a complete package with no gaps.

Snapshot:
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.

Major advantage of Snapshot over Backups is the time it takes to backup and restore especially when you want to take the backup and restore quickly in a short span of time. Referring to below image, Snapshot takes only seconds in the first step as there is no data change. In a typical deployment less than 1% of the data pages will be changed and restoration will also be very quick.

Below picture provides a better understanding of how snapshot works!

Compare Snapshot vs Backup

Snapshot vs Backup

Backup Scenarios (Full/ Differential/ Transaction Log backups):
• When I need Point in Time recovery option
• When I have to Restore week/Month/Year old backup
• Server/SAN Disk migrations, when there is a possibility of disk corruption
• During SQL Server upgrades, etc

Now… I choose typical backup strategy (Full, Differential, transactional) for my day to day backups, and I use this backup option 95% of the time but I will explain when I choose Snapshot!
Snapshot scenarios:
• During an application deployment, when there is possibility of Dev damaging my data
• Just before implementing a complex script to change data
• During Demos: I want to play with the data during a demo and go back to original version immediately after the demo

NOTE: When we use snapshot and restore the database using snapshot, it may break the log sequence and point in time recovery might get impacted.

Reference:
• Database Backup: https://msdn.microsoft.com/en-us/library/ms186865.aspx
• Database Snapshot: https://msdn.microsoft.com/en-us/library/ms175158.aspx
• Limitations and Requirements of Snapshots: https://technet.microsoft.com/en-us/library/ms189940(v=sql.105).aspx
http://solutioncenter.apexsql.com/using-sql-server-database-snapshots-protect-yourself-against-accidental-data-modification/
http://www.sqlskills.com/blogs/paul/database-snapshots-when-things-go-wrong/

Advertisements
This entry was posted in Productivity, SQL and tagged , , , , . Bookmark the permalink.

2 Responses to SQL Snapshot vs Backup – When to choose snapshot over backup

  1. Great stuff thanks for sharing ! The difference between snapshot and backup in sql server is beginning to clear up. As i found another helpful post for the same see here http://www.sqlmvp.org/sql-server-database-snapshot-vs-backup/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s