Assume that I created a database called [DBS] and inside that database I created a table called [products].
Then I inserted 4 rows into [products].
Afterwards I took a snapshot of [DBS]. As a result, sparse file Snapshot_DBS.ss was created in my drive and in its properties, the size of file was 8 MB whereas the size on disk was 128KB (see attachment).
Let's go to the interesting part.
Whenever I made either INSERT or UPDATE operations on that table the size of Snapshot_DBS.ss on disk kept growing.
In the internet I found such an explanation: A snapshot contains the original version of pages created and changes in the source since the snapshot was created.
My 1st question is, what kind of changes does snapshot file store? Is it acting like t-log (storing every single changes made to [DBS])?
The 2nd question is, the part where it states "…and changes in the source since the snapshot was created" reminds me the working principle of Differential backup where latter takes all t-logs since the full backup was taken. Is their working principle similar somehow?
Best Answer
Database snapshots operate at the data-page level and not at log level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
This is the point where log and data will change because log contains committed as well as uncommitted transactions.
Differential backup and snapshot is not same and are not related. Snapshot doesn't consider last full backup and is a static view of source database.
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.
We also need to understand purpose of these two:
Snapshots are typically useful for purposes like audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.
It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.
You may refer more details from below links:
https://www.sqlshack.com/understanding-database-snapshots-vs-database-backups-in-sql-server/
https://vijredblog.wordpress.com/2015/01/28/sql-snapshot-vs-backup-when-to-choose-snapshot-over-backup/
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-2017
Hope above helps.