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:
No comments:
Post a Comment