How to find database restore history from MSDB?

Click to subscribe to this blog

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 175 other followers
Blog Readers
- 609,605 Readers
Grab this badge here!
Disclaimer

This is a personal blog. The opinions expressed here represent my own thoughts and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have written my personal experience on this blog. Please don't direct implement or execute any query on production before have detailed study.
Database Support
- Notes (367)
  - Database (73)
  - DB Articles (34)
  - IIS (1)
  - Microsoft Technologies (1)
  - MySQL (11)
  - Networking (7)
  - Oracle (4)
  - OS and SQL (17)
  - PostGreSQL (2)
  - SQL Scripts (36)
  - SQL Server (270)
  - SQL Server 2008 (142)
  - SQL Server 2008 R2 (136)
  - SQL Server 2011 (Denali) (46)
  - Windows OS (4)
Top Rated
Follow Me

NetworkedBlogs
Blog:
SQLDBPOOL.COM
Topics:
Sql Server, Dba, Developer

Follow my blog

About Me

Jugal Shah is the author of this blog. He has eight plus years of experience as Database Administrator and Developer in the Microsoft SQL Server and MySQL.
Contact Me

Email
jugal.shah@sqldbpool.com
Chat
jshah.sqldbpool@gmail.com
Twitter
- Steps to install the PostgreSQL on windows wp.me/pdaJF-tN 2 days ago
- What is PostgreSQL? wp.me/pdaJF-tB 2 days ago
- Steps to change logged in account password on Remote Desktop Session Windows Server 2008/R2 box wp.me/pdaJF-ty 1 week ago
Follow @imjugal
SQLDBPool Facebook Page

How to find database restore history from MSDB?

Posted by Jugal Shah on July 16, 2009

MSDB database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. Again SQL Server is using MSDB for storing the history of backup, restore, log-shipping details and more…

You can query the MSDB as below to get the details about the SQL Server restore history.

SELECT TOP 10 *
FROM restorehistory WITH (nolock)WHERE (destination_database_name = 'Database Name')ORDER BY restore_date DESC
All Databases

SELECT TOP 10 * FROM restorehistory WITH (nolock)ORDER BY restore_date DESC

This entry was posted on July 16, 2009 at 12:09 pm and is filed under SQL Scripts, SQL Server. Tagged: Database restore history, MSDB. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “How to find database restore history from MSDB?”

Neeraj Kumar said

February 2, 2012 at 3:56 pm
Thanks Jugal,
I am able to find the restore history now….please again do me a favor.. i want to find who and when delete operation is performed….can we find the same for truncate??

Reply
- Jugal Shah said
  
  February 3, 2012 at 1:49 am
  You can’t fine for delete and truncate. You have to setup auditing, or C2 trace setup or you can checkout default trace if deleted recently.
  
  Reply
  - Neeraj Kumar said
    
    February 3, 2012 at 3:42 am
    I am using CDC for delete…but i am not able to find the user who deleted the record..how can we find??