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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s