Histórico dos tempos de conclusão da restauração do banco de dados?

Essas informações não são rastreadas no MSDB, portanto, não há como encontrá-las lá (a menos que a operação de restauração esteja em sua própria etapa em um trabalho; nesse caso, você pode obtê-las em msdb.dbo.sysjobhistory).

Se você não pode confiar no histórico do trabalho, e ele foi recente o suficiente, você pode aproximá-lo do rastreamento padrão, supondo que o sistema faça algo no banco de dados assim que a restauração for concluída (o que acontece em todos os meus testes, embora possa haver cenários em que isso não aconteça, como restaurar com norecovery ou restaurar um banco de dados somente leitura - ambos os cenários que não testei):

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT *, rn = ROW_NUMBER() OVER 
  (PARTITION BY DatabaseName ORDER BY StartTime)
INTO #blat
FROM sys.fn_trace_gettable(@path, DEFAULT) 
WHERE DatabaseName IN (
  N'db1', N'db2' -- , ...
)
ORDER BY StartTime DESC; 

SELECT b.DatabaseName, b.TextData, 
  ApproximateRestoreTime = DATEDIFF(MILLISECOND, b.StartTime, b2.StartTime)
FROM #blat AS b 
LEFT OUTER JOIN #blat AS b2
ON b.DatabaseName = b2.DatabaseName
AND b2.rn = b.rn + 1
WHERE b.EventClass = 115 AND b.EventSubClass = 2
ORDER BY b.StartTime DESC;

GO
DROP TABLE #blat;

(Eu tentei as consultas nesta resposta do Stack Overflow , mas elas não foram tão eficientes - você é mais do que bem-vindo para experimentá-las também, para ver se elas atendem melhor às suas necessidades.)

Um cenário que testei em que essa estratégia não funciona é se todas as opções de estatísticas automáticas estiverem desativadas - já que a atividade que parece acontecer imediatamente após a restauração são algumas operações de estatísticas automáticas em tabelas do sistema como syscolparse sysschobjs. Isso não acontece - mesmo para tabelas do sistema - quando as estatísticas automáticas estão desabilitadas.

Se você tiver carimbos de data/hora de eventos subseqüentes do sistema imediatamente após a restauração, terá uma estimativa aproximada de quanto tempo a restauração levou, mas não será necessariamente muito precisa. Você não obtém esse tipo de informação mais precisa, é claro, que é o que obtém a saída na guia de mensagens se você executar uma restauração interativamente (e também é gravada no log de erros, desde que o sinalizador de rastreamento 3226 não esteja ativado ):

RESTORE DATABASE processou com sucesso 291 páginas em 0,021 segundos (108,258 MB/s).

Mas você ainda pode extrair essas informações do log de erros (novamente, desde que tenha ocorrido recentemente o suficiente para não ter desaparecido). Se você não tiver o sinalizador de rastreamento 3226 habilitado, deverá ver todas as mensagens como acima no log de erros atual usando a seguinte consulta:

EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';

Agora, você precisa expandir seu conjunto de pesquisa depois de identificar o período de tempo desejado, pois a(s) mensagem(ns) acima não inclui(em) o nome do banco de dados. Assim, você pode rastrear três conjuntos de mensagens do log: quando o banco de dados foi marcado como restauração, quando a restauração foi concluída e a mensagem que inclui as páginas e a hora (observe que a última pode não estar alinhada com o delta entre o os dois primeiros se você estiver tentando obter granularidade abaixo do segundo).

CREATE TABLE #x(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #x EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';
-- repeat for 1, 2, 3, 4, 5, 6 if you want to capture more history:
--INSERT #x EXEC sp_readerrorlog 1, 1, N'Restore', N'MB/sec';
--INSERT #x EXEC sp_readerrorlog 2, 1, N'Restore', N'MB/sec';
--...

CREATE TABLE #y(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING';
-- again repeat for 1, 2, 3, 4, 5, 6 like above

CREATE TABLE #z(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #z EXEC sp_readerrorlog 0, 1, N'Restore is complete';
-- one more time, you may need to repeat if you want more history

SELECT LogDate, [Text]
FROM 
(
  SELECT LogDate, [Text] FROM #x
  UNION ALL SELECT LogDate, [Text] FROM #y
  UNION ALL SELECT LogDate, [Text] FROM #z
) AS xyz
ORDER BY LogDate;

GO
DROP TABLE #x, #y, #z;

Se você estiver atrás apenas de um único banco de dados ou de um padrão de nome de banco de dados, poderá adicionar um segundo filtro à segunda e à terceira consultas, por exemplo:

INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING', N'database_name';

Se o sinalizador de rastreamento 3226 estiver ativado, você receberá apenas as duas primeiras mensagens e não a terceira.

Se você deseja solucionar problemas de tempos de restauração lentos, primeiro verifique se a Inicialização instantânea de arquivo está ativada (consulte aqui e aqui ) e você também pode monitorar ativamente o processo (além da STATSopção, que não considera desfazer/refazer , trabalho VLF ou MSDB/manutenção de histórico) ativando sinalizadores de rastreamento 3604/3605, 3004 e 3014. Consulte este artigo para obter mais detalhes.

Você pode usar um pouco do script abaixo para visualizar os detalhes da última restauração de um banco de dados específico.

DECLARE @dbname sysname, @days int
SET @dbname = 'DB name here' --substitute for whatever database name you want
SET @days = -3 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type 
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

Eu estava vasculhando as interwebs em busca de uma consulta para me ajudar com esse problema e me deparei com esse tópico. Obrigado Aaron por confirmar que as tabelas msdb não capturam o tempo de conclusão da restauração, portanto, calcular a duração apenas dessa fonte é impossível! Além de usar o rastreamento padrão como Aaron descreve, se você tiver ERRORLOGs disponíveis na época em que a restauração foi iniciada e concluída, você pode calcular a duração de outra maneira com alguma tolice. Para mim, meus logs de erros eram muito mais antigos do que meus arquivos de rastreamento padrão, portanto, essa abordagem forneceu muito mais informações históricas para mim.

Criei um script abaixo que deve fazer isso para você, desde que você tenha as permissões elevadas adequadas para chamar os sps estendidos:

DECLARE @NumErrorLogs INT, @i INT = 0

-- Get number of ERRORLOG files we need to iterate through
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT;

-- Table to capture restore completion times
CREATE TABLE #tmp_Restore
(
        LogDate DATETIME2
    ,   ProcessInfo NVARCHAR(250)
    ,   [Text] NVARCHAR(4000)
)

-- Pull any Restore Events from available Logs
WHILE @i < @NumErrorLogs
BEGIN
    INSERT INTO #tmp_Restore
    EXEC xp_readerrorlog @i, 1, N'Restore is complete on database', NULL, NULL, NULL, N'desc'

    SELECT @i = @i + 1
END

-- Create new column that will store database name parsed out from text field
ALTER TABLE #tmp_Restore ADD [database] NVARCHAR(250);

-- Update database name column
UPDATE #tmp_Restore
SET [database] = SUBSTRING([Text], 34, LEN([Text]) - CHARINDEX(N'''', [Text]) - CHARINDEX(N'''', REVERSE([Text])))


-- Build out restore order so joins match up in case a database has been restored multiple times
;WITH rh AS (
    SELECT  rh.destination_database_name AS database_name
        ,   rh.restore_date AS restore_start
        ,   ROW_NUMBER() OVER (PARTITION BY rh.destination_database_name ORDER BY rh.restore_date DESC) AS restore_num
    FROM    msdb.dbo.restorehistory rh
), r AS (
    SELECT  r.[database] AS database_name
        ,   r.LogDate AS restore_finish
        ,   ROW_NUMBER() OVER (PARTITION BY r.[database] ORDER BY r.LogDate DESC) AS restore_num
    FROM    #tmp_Restore r
)
-- Show restore durations for those database we have available history for
SELECT  r.database_name
    ,   rh.restore_start
    ,   r.restore_finish
    ,   DATEDIFF(MINUTE, rh.restore_start, r.restore_finish) AS restore_duration
FROM    rh
    INNER JOIN r
        ON rh.database_name = r.database_name
        AND rh.restore_num = r.restore_num
ORDER BY r.restore_finish DESC

-- Cleanup
DROP TABLE #tmp_Restore

Espero que ajude alguém no caminho!