Início / dba / Perguntas / 161820
Accepted
SQL_Deadwood
Asked: 2017-01-21 14:35:40 +0800 CST

Encontre a duração MIN e MAX do backup do banco de dados

  • 772

Eu tenho essa consulta para encontrar a duração dos backups feitos que desejo executar em servidores registrados no CMS em toda a empresa.

O problema, porém, é como encontrar apenas a duração MINe MAXpor banco de dados em vez do tempo de execução de todos os backups.

Eu brinquei muito com MINe MAX, mas ainda não encontrei a combinação correta.

Estou indo sobre isso da maneira errada?

DECLARE @dbname sysname

SET @dbname = NULL 

SELECT database_name AS [Database]
    , backup_start_date AS [Backup Started]
    , backup_finish_date AS [Backup Finished]
    , backup_size AS [Backup Size]
    , compressed_backup_size AS [Compressed Backup Size]
    , CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset
WHERE [type]!='L' 
    AND database_name IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name
    ,backup_start_date
    ,backup_finish_date
    ,backup_size
    ,compressed_backup_size;

Código originado daqui .

sql-server t-sql

1 respostas

  1. Best Answer

    Use a ROW_NUMBER()agregação para obter as linhas mínima e máxima e mostre apenas essas linhas:

    ;WITH cte AS (
    SELECT d.name
        , bs.backup_start_date
        , bs.backup_finish_date
        , DurationInSeconds = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        , RnMin = ROW_NUMBER() OVER (PARTITION BY d.name 
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ASC)
        , RnMax = ROW_NUMBER() OVER (PARTITION BY d.name 
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) DESC)
    FROM msdb.dbo.backupset bs
        INNER JOIN master.sys.databases d ON bs.database_name = d.name
)
SELECT cte.name
    , cte.backup_start_date
    , cte.backup_finish_date
    , cte.DurationInSeconds
FROM cte
WHERE cte.RnMax = 1
    OR cte.RnMin = 1
ORDER BY cte.name
    , cte.backup_start_date;
    • 4

relate perguntas