Início / dba / Perguntas / 343419
Accepted
Marcello Miorelli
Asked: 2024-11-05 21:24:52 +0800 CST

como obter a localização do backup também? você pode usar múltiplas colunas pivotando ou qualquer outra maneira!

  • 772

Recebi esta ótima consulta que mostra o backup mais recente para cada banco de dados existente .

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'
);

Notei que ele usa pivotamento. E se eu tivesse que adicionar o local também? Seriam várias colunas .

Seja dinamizando usando múltiplas colunas ou desdinamizando , como eu poderia adicionar a localização de cada tipo de backup à consulta acima?

basicamente onde está o último backup completo, onde está o último diff e o último log (se houver).

este é o script que uso para ver a localização do backup:

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

a questão é:

como eu adicionaria o local do backup (cada tipo) à primeira consulta?

sql-server

1 respostas

  1. Best Answer

    Fiz vários ajustes para que funcionasse. Optei por não usar a sintaxe PIVOT, em vez disso, utilizei cláusulas CASE WHENwith GROUP BYpara pivotar várias colunas.

    1. Consulta mais interna: recupera informações sobre backups (incluindo data e local) para todos os bancos de dados de usuários. Ela usa uma função ROW_NUMBER(), particionada por nome do banco de dados e tipo de backup, para atribuir um número de linha exclusivo a cada entrada de backup, ordenado pela data de término do backup em ordem decrescente (para que o backup mais recente tenha rn=1).

    2. Segunda consulta interna: filtra para reter apenas o backup mais recente por banco de dados e tipo de backup, selecionando linhas com rn = 1.

    3. Consulta externa: usa instruções CASE WHEN para dinamizar os dados, permitindo que várias colunas (como data de término e local) por tipo de backup sejam exibidas para cada linha do banco de dados.

    -- 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

    A tabela de resultados fica assim:A tabela de resultados da consulta

    • 2

relate perguntas