如何查询SQL Server数据库还原所需的时间？

Im trying to write a query that will tell me how much time a restore (full or log) has taken on SQL server 2008.

我试图编写一个查询，告诉我在SQL Server 2008上恢复（完整或日志）的时间。

I can run this query to find out how much time the backup took:

我可以运行此查询以查明备份花费了多少时间：

select  database_name, 
        [uncompressed_size] = backup_size/1024/1024,
        [compressed_size] = compressed_backup_size/1024/1024, 
        backup_start_date, 
        backup_finish_date, 
        datediff(s,backup_start_date,backup_finish_date) as [TimeTaken(s)], 
from    msdb..backupset b 
where   type = 'L' -- for log backups
order by b.backup_start_date desc

This query will tell me what is restored but now how much time it took:

此查询将告诉我恢复了什么，但现在花了多少时间：

select * from msdb..restorehistory

restorehistory has a column backup_set_id which will link to msdb..backupset, but that hold the start and end date for the backup not the restore.

restorehistory有一个列backup_set_id，它将链接到msdb..backupset，但保存备份的开始和结束日期而不是还原。

Any idea where to query the start and end time for restores?

知道在哪里查询恢复的开始和结束时间？

3 个解决方案

#1

To find the RESTORE DATABASE time, I have found that you can use this query:

要查找RESTORE DATABASE时间，我发现您可以使用此查询：

declare @filepath nvarchar(1000) 

SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL) 
WHERE [property] = 2 and traceid=1 

SELECT *
FROM [fn_trace_gettable](@filepath, DEFAULT) 
WHERE TextData LIKE 'RESTORE DATABASE%' 
ORDER BY StartTime DESC;

The downside is, you'll notice that, at least on my test server, the EndTime is always NULL.

缺点是，您会注意到，至少在我的测试服务器上，EndTime始终为NULL。

So, I came up with a second query to try and determine the end time. First of all, I apologize that this is pretty ugly and nested like crazy.

所以，我想出了第二个查询来尝试确定结束时间。首先，我很抱歉这很丑陋并且像疯了一样嵌套。

The query below assumes the following:

以下查询假定以下内容：

When a restore is run, for that DatabaseID and ClientProcessID, the next EventSequence contains the TransactionID we need.
运行还原时，对于该DatabaseID和ClientProcessID，下一个EventSequence包含我们需要的TransactionID。
I then go and find the max EventSequence for the Transaction
然后我去找到事务的最大EventSequence
Finally, I select the record that contains RESTORE DATABASE and the maximum transaction associated with that record.
最后，我选择包含RESTORE DATABASE的记录以及与该记录关联的最大事务。

I'm sure someone can probably take what I've done and refine it, but this appears to work on my test environment:

我相信有人可能会采取我已经完成的工作并对其进行改进，但这似乎适用于我的测试环境：

declare @filepath nvarchar(1000) 

SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL) 
WHERE [property] = 2 and traceid=1 

SELECT *
FROM [fn_trace_gettable](@filepath, DEFAULT) F5
INNER JOIN 
(
    SELECT F4.EventSequence MainSequence, 
         MAX(F3.EventSequence) MaxEventSequence, F3.TransactionID
    FROM [fn_trace_gettable](@filepath, DEFAULT) F3
    INNER JOIN 
    (
        SELECT F2.EventSequence, MIN(TransactionID) as TransactionID
        FROM [fn_trace_gettable](@filepath, DEFAULT) F1
        INNER JOIN 
        (
            SELECT DatabaseID, SPID, StartTime, ClientProcessID, EventSequence
            FROM [fn_trace_gettable](@filepath, DEFAULT)
            WHERE TextData LIKE 'RESTORE DATABASE%' 
        ) F2 ON F1.DatabaseID = F2.DatabaseID AND F1.SPID = F2.SPID 
                       AND F1.ClientProcessID = F2.ClientProcessID 
                       AND F1.StartTime > F2.StartTime
        GROUP BY F2.EventSequence
    ) F4 ON F3.TransactionID = F4.TransactionID 
    GROUP BY F3.TransactionID, F4.EventSequence
) F6 ON F5.EventSequence = F6.MainSequence 
    OR F5.EventSequence = F6.MaxEventSequence
ORDER BY F5.StartTime

EDIT

编辑

I made some changes to the query, since one of the test databases I used is case-sensitive and it was losing some records. I also noticed when restoring from disk that the DatabaseID is null, so I'm handling that now as well:

我对查询进行了一些更改，因为我使用的测试数据库之一是区分大小写的，并且它丢失了一些记录。我还注意到从磁盘恢复时DatabaseID为null，所以我现在也在处理它：

SELECT * 
FROM [fn_trace_gettable](@filepath, DEFAULT) F5 
INNER JOIN  
( 
    SELECT F4.EventSequence MainSequence,  
         MAX(F3.EventSequence) MaxEventSequence, F3.TransactionID 
    FROM [fn_trace_gettable](@filepath, DEFAULT) F3 
    INNER JOIN  
    ( 
        SELECT F2.EventSequence, MIN(TransactionID) as TransactionID 
        FROM [fn_trace_gettable](@filepath, DEFAULT) F1 
        INNER JOIN  
        ( 
            SELECT DatabaseID, SPID, StartTime, ClientProcessID, EventSequence 
            FROM [fn_trace_gettable](@filepath, DEFAULT) 
            WHERE upper(convert(nvarchar(max), TextData)) 
                LIKE 'RESTORE DATABASE%'  
        ) F2 ON (F1.DatabaseID = F2.DatabaseID OR F2.DatabaseID IS NULL) 
                   AND F1.SPID = F2.SPID  
                   AND F1.ClientProcessID = F2.ClientProcessID  
                   AND F1.StartTime > F2.StartTime 
        GROUP BY F2.EventSequence 
    ) F4 ON F3.TransactionID = F4.TransactionID  
    GROUP BY F3.TransactionID, F4.EventSequence 
) F6 ON F5.EventSequence = F6.MainSequence  
    OR F5.EventSequence = F6.MaxEventSequence 
ORDER BY F5.StartTime

#2

Make it a Job. Then run it as the Job. Then check the View Job History. Then look at the duration column.

让它成为一份工作。然后将其作为Job运行。然后查看查看作业历史记录。然后查看持续时间列。

#3

While it is running you can check something like this dmv.

在它运行时你可以检查像这样的dmv。

select 
d.name
,percent_complete
,dateadd(second,estimated_completion_time/1000, getdate())
, Getdate() as now
,datediff(minute, start_time
, getdate()) as running
, estimated_completion_time/1000/60 as togo
,start_time
, command 
from sys.dm_exec_requests req
inner join sys.sysdatabases d on d.dbid = req.database_id
where 
req.command LIKE '%RESTORE%'

Or you can use some magic voodoo and interpret the transaction log in the following table function, however the only person I know to understand any info in this log is Paul Randal. I Know he sometimes checks Server Fault, but don't know if he wonders *.

或者你可以使用一些魔法伏都教并解释下表功能中的事务日志，但是我知道唯一了解此日志中任何信息的人是Paul Randal。我知道他有时检查服务器故障，但不知道他是否想知道*。

select * from fn_dblog(NULL,NULL)

select * from fn_dblog（NULL，NULL）

Hope this helps. If you manage to use this and find a solution please tell us.

希望这可以帮助。如果您设法使用此功能并找到解决方案，请告诉我们。

Good Luck!

祝你好运！

#1