Wednesday, November 25, 2015

Who restored those databases?!

Working an upgrade with a customers right now, from SQL Server v2005 to v2012. The bulk of the work is done, but I am still rebuilding an ETL project for them. The new VS solution is in place, I've upgraded 50+ packages successfully, and now I just need to build the Agent job that will kick off the ETL each day. Everything is progressing very well, but... today I was surprised!  

I get into SSMS, SQL Server Agent\Jobs, and find a TON of jobs there that I know nothing about! The only thing I can tell for sure is that they are SSRS subscriptions. You know, those crazy job names like this:

That tells me ReportServer, but remember, this is a new box.  Not production-ready yet. I didn't even know there was a ReportServer database until now. Hence, my surprise. This post is just a quick collect on the last database restore details from msdb..restorehistory, if exists.

    /* get the most recent restore details from your databases */
    WITH LastRestore AS
    (
    SELECT
       sd.[name] [Database],
       rh.restore_date [RestoreDate],
       rh.destination_database_name [RestoredTo],
       rh.user_name [RestoredBy],
     RowNum = ROW_NUMBER() OVER (PARTITION BY sd.Name ORDER BY rh.[restore_date] DESC)
    FROM
       master.sys.databases sd INNER JOIN msdb.dbo.[restorehistory] rh
         ON sd.[name] = rh.[destination_database_name]    )

    -- bring back the details
    SELECT
       [Database],
       RestoreDate,
       RestoredTo,
       RestoredBy
    FROM
       LastRestore
    WHERE 
       RowNum = 1
       AND RestoreDate IS NOT NULL

This pic is from my customer's instance..though I've blacked out RestoredBy.  :-)







And this one is from my own instance, just so you can see the full output:
      

I've actually posted this before here, but it was for another purpose, and I didn't include in the output who actually did the deed. Pretty helpful to know who's doing what.  We all know, it happens.  

See this for more information on msdb..restorehistory:


1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete