HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Why database snapshot creation is faster than normal database backups?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whybackupsthannormaldatabasesnapshotfastercreation

Problem

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

Solution

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

Context

StackExchange Database Administrators Q#137225, answer score: 4

Revisions (0)

No revisions yet.