Where is snapshot restore histort?

Question

Where is snapshot restore histort?

Igor Marchenko

Hall of Fame

Points: 3281
More actions
January 25, 2008 at 7:37 pm

#202720

Hello!
I created database snapshot in SQL Server 2005 and was restoring database from it. I was wondering if there is a system table that stored history of snapshot restores similar to msdb.dbo.restorehistory.
Thanks,
Igor

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Yes - the restorehistory table has what you want. Look in the restore_type column for type 'R' - which corresponds to reverts. Beware also that reverting from a database snapshot breaks your log backup chain as the transaction log is rebuilt.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 2

Hello Paul,

Thanks for you reply! I did check this table and found no entries with restore_type R. This is weird as I definitely reverted to snapshot several time.

I thanks for your reminder about transaction log 🙂 I am always cautious about it. But this is purely development environment.

Thanks again,

Igor

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Hmm - could be a bug. I'm going to blog about this today - I'll investigate and let you know what I find.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 4

Igor Marchenko

Hall of Fame

Points: 3281

January 28, 2008 at 4:14 pm

#772687

Paul,

Thanks for looking into this!

Igor

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Well, its broken. When you do a revert there's nothing entered into the restorehistory table. The only way to tell that a database has been reverted is to look in the errorlog where there is a notification message. I'll have a blog up on this in an hour or so.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 6

Thanks again Paul! I am just surprised that nobody noticed this before.

Igor