主页 / dba / 问题 / 161820
Accepted
SQL_Deadwood
Asked: 2017-01-21 14:35:40 +0800 CST

查找 MIN 和 MAX 数据库备份持续时间

  • 772

我有这个查询来查找我想要针对整个企业的 CMS 中的注册服务器运行的备份的持续时间。

但问题是如何只找到每个数据库的备份时间MIN和MAX持续时间，而不是所有备份的运行时间。

我已经搞砸了很多，MIN但MAX还没有找到正确的组合。

我会以错误的方式解决这个问题吗？

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;

代码来自这里。

sql-server t-sql

1 个回答

  1. Best Answer

    使用ROW_NUMBER()聚合获取最小和最大行，然后仅显示这些行：

    ;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

相关问题