Restore history from Snapshot

• Ashwani Malik

  Ten Centuries

  Points: 1140

  More actions

  August 9, 2010 at 9:49 am

  #222293

  I did not get any responses so I am reposting on this forum from http://www.sqlservercentral.com/Forums/Topic964302-391-1.aspx

  Is there any historical system tables or catalog views to find out if the database was reverted back from the snapshot?

  Here are the scripts to create scenario

  --Create Snapshot

  CREATE DATABASE AdventureWorksSnap08052010 ON

  ( NAME = AdventureWorks_Data, FILENAME =

  'E:\MSSQL\Data\AdventureWorksSnap08052010.ss' )

  AS SNAPSHOT OF AdventureWorks;

  GO

  select * from HumanResources.Employee

  select * from AdventureWorksSnap08052010.HumanResources.Employee

  Update HumanResources.Employee Set HireDate='1996-07-30' where

  EmployeeID=1

  --Revert

  USE master;

  RESTORE DATABASE AdventureWorks from

  DATABASE_SNAPSHOT = 'AdventureWorksSnap08052010';

  GO

  Select * from msdb.dbo.restorehistory order by restore_date

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  August 9, 2010 at 10:22 am

  #1204273

  According to Paul Randal' blog msdb.dbo.restorehistory is not populated by a restore from a snapshot, at least in SQL 2005:

  http://www.sqlskills.com/blogs/paul/category/Database-Snapshots.aspx

  "So how can I tell when the database was reverted? Books Online documents that the restorehistory table in msdb should have an entry with restore_type = 'R'. Let's try:

  SELECT * FROM msdb.dbo.restorehistory WHERE [destination_database_name] = 'SnapshotTest';

  GO

  Nope - nothing. That functionality seems to be broken. The only way I could find to trace reverting was in the error log:

  2008-01-30 11:09:21.73 spid53 Reverting database 'SnapshotTest' to the point in time of database snapshot 'ST_Snap' with split point LSN 26000000013800001 (0x0000001a:0000008a:0001). This is an informational message only. No user action is required.

  2008-01-30 11:09:21.74 spid53 Starting up database 'SnapshotTest'.

  2008-01-30 11:09:21.74 spid53 The database 'SnapshotTest' is marked RESTORING and is in a state that does not allow recovery to be run.

  2008-01-30 11:09:21.85 spid53 Starting up database 'SnapshotTest'.

  2008-01-30 11:09:21.87 spid53 Starting up database 'ST_Snap'.

  "

• Ashwani Malik

  Ten Centuries

  Points: 1140

  More actions

  August 9, 2010 at 10:52 am

  #1204291

  Finally someone replied.Thanks a lot for your help. I thought so too, but just wanted to confirm. Really appreciate your response.