3

Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server

4

Mostly because a snapshot isn't really a backup.

As explained in How database snapshots work

The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space.

A snapshot doesn't actually contain any data until you start making changes to the original database. Basically any time you change a page in the database that original page gets written to the snapshot. If you then make another change to the same page nothing needs to happen since the original already exists in the snapshot. Because of this the size of the snapshot will be from 0 to the size of the database at the time of the snapshot.

So when you restore back to the snapshot it just writes back those changed pages.

A backup (a full one at least) is a copy of the database written to a file.

When you restore from backup it writes ALL of the pages, not just changes (again assuming a full backup).

That's why creating/recovering from a snapshot is so much faster.

Here is some further reading:

Database Snapshots

1

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup

Full backup includes all committed and uncommitted transaction when full backup has finished. Read more about this in Understanding SQL Server Backups full backup also includes few amount of transaction log, if required, this is not the case with snapshot backups. Backing of transaction log is required so that recovery can run successfully when backup is restored.

Read about Database snapshot A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.