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!
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.
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!
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!
• 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.
• 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