How to find database restore history from MSDB?

Written by

Jugal Shah

in

, , ,

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

Comments

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

  1. Neeraj Kumar Avatar
    Neeraj Kumar

    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
    1. Jugal Shah Avatar
      Jugal Shah

      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
      1. Neeraj Kumar Avatar
        Neeraj Kumar

        I am using CDC for delete…but i am not able to find the user who deleted the record..how can we find??

Thanks for the comment, will get back to you soon… Jugal Shah

More posts