CODEWITHSUNDEEP
sql-servert-sqlssms
John Waclawski
John Waclawski

Reputation: 996

T-SQL - Get runduration from a restore job

I'm using the code below to pull 3 columns:

Code:

WITH LastRestores
AS 
(
    SELECT 
        @@SERVERNAME AS [ServerName],
        [d].[name] AS [DatabaseName],
        CONVERT(VARCHAR, r.restore_date, 100) AS [RestoreDateTime],
        RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME 
                                    ORDER BY r.[restore_date] DESC)
    FROM 
        master.sys.databases d
    LEFT OUTER JOIN 
        msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME
)
SELECT 
    [ServerName], [DatabaseName], [RestoreDateTime]
FROM 
    [LastRestores]
WHERE 
    [RowNum] = 1
    AND DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
    AND CONVERT(VARCHAR(12), [RestoreDateTime]) = CONVERT(VARCHAR(12), GETDATE())

What I'd like to do is get the RunDuration that is tied in with the msdb..sysjobhistory table. I can't, for the life of me, see how to tie that table in with my code above. I'm sure there is a round about way to get the info I am looking for.

Any suggestions?

Thanks!

Upvotes: 1

Views: 67

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1169

You can find the end datetime of a restore in the SQL Server logs. You can use xp_readerrorlog to read the logs. Please note that the format of the message may be different depending on your version. This code works in SQL Server 2012.

CREATE TABLE #t (LogDate DATETIME, ProcessInfo VARCHAR(100), RowVal VARCHAR(512));

INSERT INTO #t
EXEC master.dbo.xp_readerrorlog;

WITH RestoreDate AS (
    SELECT
        LEFT(REPLACE(RowVal, 'Database was restored: Database: ', ''), CHARINDEX(', creation', REPLACE(RowVal, 'Database was restored: Database: ', '')) - 1) AS DbName,
        LogDate
    FROM
        #t
    WHERE
        RowVal LIKE 'Database was restored: Database: %'),
    LastRestores AS (
    SELECT 
        @@SERVERNAME AS [ServerName],
        [d].[name] AS [DatabaseName],
        CONVERT(VARCHAR, r.restore_date, 100) AS [RestoreDateTime],
        RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME 
                                    ORDER BY r.[restore_date] DESC)
    FROM 
        master.sys.databases d
    LEFT OUTER JOIN 
        msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME)
SELECT 
    l.[ServerName],
    l.[DatabaseName],
    l.[RestoreDateTime],
    MAX(r.LogDate) AS LogDate,
    CONVERT(VARCHAR, MAX(r.LogDate), 100) AS FormattedLogDate
FROM 
    [LastRestores] l
JOIN
    RestoreDate r
        ON l.DatabaseName = r.DbName
WHERE 
    l.[RowNum] = 1
    AND l.DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
    AND CONVERT(VARCHAR(12), [RestoreDateTime]) = CONVERT(VARCHAR(12), GETDATE())
GROUP BY
    l.[ServerName],
    l.[DatabaseName],
    l.[RestoreDateTime];

DROP TABLE #t;

Upvotes: 2

Related Questions