3

Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics which were created for these thousand of queries?

2
  • 1
    As stated in BOL: any updates to the database since the snapshot's creation are lost. msdn.microsoft.com/en-us/library/ms179557(v=sql.100).aspx
    – user507
    Apr 27, 2015 at 11:13
  • 2
    Database snapshots are at the page level. All changes to the source database will be stored in the snapshot and used to revert back to the original state. This includes pages that store user data, meta-data, and internal structures.
    – Dan Guzman
    Apr 27, 2015 at 11:50

2 Answers 2

4

Yes you would:

The original source database is overwritten by the reverted database, so any updates to the database since the snapshot's creation are lost.

and

The metadata of a reverted database is the same as the metadata at the time of the snapshot.

Quotes from Revert a Database to a Database Snapshot in the MSDN Library.

2

Further to Sporri's answer - you can check this if you are using SSMS.

To view a database snapshot on a server:

  1. In Object Explorer, connect to the instance of the Microsoft SQL Server >Database Engine and then expand that instance.
  2. Expand Databases.
  3. Expand Database Snapshots, and select the snapshot you want to view.

When you do this, it gives you the list of objects that are included in the Snapshot, as it would with a normal database (right down to Statistics etc.)

Taken from: https://msdn.microsoft.com/en-us/library/ms175469(v=sql.105).aspx

2
  • 3
    I'd love to know why I got a down vote for this - using this method gives you extra visibility of what you are getting from a snapshot - which added to the discussion and possibly/hopefully the OP's understanding.
    – MHSQLDBA
    Apr 27, 2015 at 13:35
  • Thanks up-voters. Would still like to know why the original person downvoted me, but I will make do with Justice!
    – MHSQLDBA
    Apr 30, 2015 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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