Início / dba / Perguntas / 301602
Accepted
SqlDBA
Asked: 2021-10-25 15:41:36 +0800 CST

gerar alerta se alguém acionar backup ad-hoc e não for backup copy_only

  • 772

Estamos usando uma ferramenta de terceiros para fazer backup de bancos de dados do servidor SQL. Estou procurando configurar um alerta que é gerado sempre que o backup Ad-hoc é feito e não é copy_only. Estamos usando a ferramenta de monitoramento de terceiros e gostaria de configurar um alerta usando esta ferramenta de terceiros que extrai uma informação usando o script T-SQL.

O alerta deve ser gerado sempre que a consulta T-SQL retornar o resultado 0 ou 1 se o backup ad-hoc for realizado na última 1 hora e não for backup de banco de dados copy_only

sql-server backup

2 respostas

  1. Best Answer

    Primeiro, você deseja recuperar as informações normais do histórico de backup na instância relevante do SQL Server, para descobrir como é a localização normal do arquivo de backup. Você pode conseguir isso com algo assim:

    SELECT  
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   CASE  
       WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk'
       WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape'
       WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' 
       WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' 
       WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device'
       ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!'    
   END AS device_type_desc,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'  
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
       ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id
WHERE 1 = 1
ORDER BY msdb.dbo.backupset.backup_start_date DESC;

    Um conjunto de resultados ao usar o Commvault como sua solução de backup pode ser assim:

    nome do banco de dados backup_start_date tipo_de_backup device_type_desc nome_dispositivo_físico nome_do_dispositivo_lógico nome_conjunto de backup Descrição is_copy_only is_snapshot nome software_name nome de usuário
    TVDTools 2021-10-25 08:04:14.000 Registro Dispositivo virtual 8e5bda3c-d453-4328-b180-dcee74549f68 NULO Backup Galaxy CommVault NULO 0 0 NULO Microsoft SQL Server DOMÍNIO\USUÁRIO
    TVDTools 24/10/2021 21:01:58.000 Registro Dispositivo virtual f40f904f-e197-4c2b-88fe-b9cdb5ee237f NULO Backup Galaxy CommVault NULO 0 0 NULO Microsoft SQL Server DOMÍNIO\USUÁRIO
    mestre 2021-10-24 20:16:02.000 Cheio Dispositivo virtual 361ff522-8e39-406c-b971-5808ee51240b NULO Backup Galaxy CommVault NULO 0 0 NULO Microsoft SQL Server DOMÍNIO\USUÁRIO
    TVDTools 24/10/2021 20:06:44.000 Diferença Dispositivo virtual 3589d70e-af8d-469f-ad66-5e1ca52cef1e NULO Backup Galaxy CommVault NULO 0 0 NULO Microsoft SQL Server DOMÍNIO\USUÁRIO
    msdb 24/10/2021 20:06:44.000 Diferença Dispositivo virtual 12685e62-f688-4818-96d3-e20a802af6b9 NULO Backup Galaxy CommVault NULO 0 0 NULO Microsoft SQL Server DOMÍNIO\USUÁRIO

    Portanto, você precisa determinar com sua solução de backup quais colunas informam quando algo está sendo executado fora de sua solução de backup.

    No meu caso, eu apenas procuraria por qualquer conjunto de backup que não tivesse CommVault Galaxy%na backupset_namecoluna e possivelmente que não estivesse usando um device_typecódigo de 7. Sua milhagem pode variar.

    Respondendo suas perguntas

    A combinação da consulta acima com algumas WHEREcláusulas resultaria na seguinte consulta, que você poderia conectar à sua ferramenta de monitoramento:

    SELECT  
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   CASE  
       WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk'
       WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape'
       WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' 
       WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' 
       WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device'
       ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!'    
   END AS device_type_desc,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'  
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
       ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id
WHERE 1 = 1
-- Is Not CommVault Backup Check
AND (msdb.dbo.backupmediafamily.device_type <> 7 
     AND msdb.dbo.backupset.name NOT LIKE 'CommVault Galaxy%')
-- Last Hour Check 
AND   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102)) >= DATEADD(hh,-1,GETDATE())
-- Is Not Copy_Only Check
AND msdb.dbo.backupset.is_copy_only <> 1
ORDER BY msdb.dbo.backupset.backup_start_date DESC;

    O que resulta na seguinte saída:

    nome do banco de dados backup_start_date tipo_de_backup device_type_desc nome_dispositivo_físico nome_do_dispositivo_lógico nome_conjunto de backup Descrição is_copy_only is_snapshot nome software_name nome de usuário (Sem nome de coluna)
    TVDTools 2021-10-25 09:21:07.000 Cheio Disco H:\adhoc\SERVERNAME\TVDTools_Full_CopyOnly_20211025_092107.bak NULO TVDTools-Backup Completo NULO 0 0 NULO Microsoft SQL Server DOMÍNIO/Usuário EOR

    Peguei vocês!

    Os resultados podem ser usados ​​em seu software de monitoramento para fazer o que você quiser, por exemplo, enviar um e-mail com os resultados, acionar um alarme no console, ...

    • 1

  2. Confira a tabela de backupset em msdb. Uma consulta de amostra para você começar:

    SELECT [database_name] AS [Database]
    , [user_name] AS [BackupCreator]
    , [backup_start_date] AS [BackupDate]
    , CASE [type]
        WHEN 'L' THEN 'Log'
        WHEN 'I' THEN 'Diff'
        WHEN 'D' THEN 'Full'
      END AS [BackupType]
    , [is_copy_only] AS [IsCopyOnly]
FROM msdb..backupset

    Pode ser necessário incluir backupmediafamily se você também quiser encontrar o local em que o backup incorreto foi feito.

    • 0

relate perguntas