主页 / dba / 问题 / 343419
Accepted
Marcello Miorelli
Asked: 2024-11-05 21:24:52 +0800 CST

如何获取备份的位置？您可以使用多列旋转或任何其他方式！

  • 772

我得到了这个很棒的查询，它显示了每个现有数据库的最新备份。

SELECT
    M.name,
    [Recovery Model] = 
        M.recovery_model_desc,
    [State] = 
        M.state_desc,
    [Last Full Backup] = 
        FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last Differential Backup] = 
        FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last log Backup] = 
        FORMAT(ISNULL(M.L, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
    SELECT
        db.name,
        db.state_desc,
        db.recovery_model_desc,
        a.type,
        a.backup_finish_date
    FROM master.sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS a
      ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
    MAX(backup_finish_date)
    FOR type IN
    (
        D,
        I,
        L
    )
) AS M --ostRecentBackup
WHERE name NOT IN
(
    N'master',
    N'msdb',
    N'model',
    N'tempdb'
);

我注意到它使用了枢轴 - 如果我还必须添加位置怎么办？那将是多列。

无论是通过使用多列进行旋转，还是逆旋转，我怎样才能将每种备份类型的位置添加到上述查询中？

基本上，最新的完整备份在哪里，最新的差异和最新的日志（如果有）在哪里。

这是我用来查看备份位置的脚本：

SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.TYPE,
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupset.backup_size * 1.024 /1024/1024 as [Backup size in MB],
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
--WHERE msdb.dbo.backupset.database_name = 'PivCRM_Prod_Online_ED'
--AND TYPE = 'D'  -- L D and I
ORDER BY msdb.dbo.backupset.backup_start_date DESC

问题是：

我该如何将备份的位置（每种类型）添加到第一个查询？

sql-server

1 个回答

  1. Best Answer

    我做了一些调整以使其正常工作。我选择不使用 PIVOT 语法，而是利用CASE WHENwithGROUP BY子句来透视多个列。

    1. 最内层查询：检索所有用户数据库的备份信息（包括日期和位置）。它使用 ROW_NUMBER() 函数（按数据库名称和备份类型分区）为每个备份条目分配一个唯一的行号，并按备份完成日期降序排列（以便最新备份的 rn=1）。

    2. 第二个内部查询：通过选择 rn = 1 的行，过滤器仅保留每个数据库和备份类型的最新备份。

    3. 外部查询：使用 CASE WHEN 语句来透视数据，允许每个备份类型的多个列（例如完成日期和位置）显示在每个数据库行中。

    -- The outer query to pivot using CASE WHEN
SELECT name,
    [Recovery Model] = recovery_model_desc,
    [State] = state_desc,
    FORMAT(ISNULL(MAX(CASE WHEN type = 'D' THEN backup_finish_date END), '19000101'), 'dd-MM-yyyy hh:mm') AS [Last Full Backup],
    MAX(CASE WHEN type = 'D' THEN physical_device_name END) AS [Last Full Backup Physical Location],
    MAX(CASE WHEN type = 'D' THEN logical_device_name END) AS [Last Full Backup Logical Location],
    FORMAT(ISNULL(MAX(CASE WHEN type = 'L' THEN backup_finish_date END), '19000101'), 'dd-MM-yyyy hh:mm') AS [Last Log Backup],
    MAX(CASE WHEN type = 'L' THEN physical_device_name END) AS [Last Log Backup Physical Location],
    MAX(CASE WHEN type = 'L' THEN logical_device_name END) AS [Last Log Backup Logical Location],
    FORMAT(ISNULL(MAX(CASE WHEN type = 'I' THEN backup_finish_date END), '19000101'), 'dd-MM-yyyy hh:mm') AS [Last Differential Backup],
    MAX(CASE WHEN type = 'I' THEN physical_device_name END) AS [Last Differential Physical Backup Location],
    MAX(CASE WHEN type = 'I' THEN logical_device_name END) AS [Last Differential Logical Backup Location]
FROM
(
    -- The second inner select to select the latest backup for each database type of each db 
    SELECT Name,
           recovery_model_desc,
           state_desc,
           type,
           backup_finish_date,
           physical_device_name,
           logical_device_name
    FROM
    (
        -- The first inner select to select all backup details
        SELECT
            db.Name,
            db.recovery_model_desc,
            db.state_desc,
            a.type,
            a.backup_finish_date,
            b.physical_device_name,
            b.logical_device_name,
            ROW_NUMBER() OVER (PARTITION BY db.Name, a.type ORDER BY a.backup_finish_date DESC) rn
        FROM master.sys.databases AS db
        LEFT OUTER JOIN msdb.dbo.backupset AS a
            ON a.database_name = db.name
        LEFT JOIN msdb.dbo.backupmediafamily b
            ON a.media_set_id = b.media_set_id
        WHERE db.Name NOT IN
        (
            N'master',
            N'msdb',
            N'model',
            N'tempdb'
        ) 
    ) Backups
    WHERE rn = 1
) LatestBackups
GROUP BY name
    , recovery_model_desc
    , state_desc
ORDER BY name

    结果表如下所示：查询结果表

    • 2

相关问题