Setting up a database snapshot

A databasesnapshot is a point in time view of a given database. Imagine that you have the Adventureworks database and you want to have a view of the database pr. one given date and time. In that situation you want a database snapshot of the Adventure works database.

The syntax is:

Create database adventureworks_yyyymmdd on
(Name=adventureworks,  Filename=’d:\sqltests\Snapshots\adventureworksSnapshot.sss’)
as snapshot of adventureworks

What you have created now is a databasesnapshot that you can query like all other “databases”, the only difference is that the size of this database is 0.

When you create a snapshot, then it is empty until a page is changed. Before a page is changed, then the original page is moved to the snapshot and then the page is changed.

So when you query a snapshot, then the query first check if a page is changed since the snapshot. If so, then it is taken from the snapshot, otherwise it is taken from the original databasefile.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *