Backups diferenciais não funcionam

Seu comando para examinar as informações de backup poderia ser expandido por algumas colunas. Duas que vêm à mente são is_snapshote is_copy_onlye então o aplicativo que está executando o backup: software_name.

--------------------------------------------------------------------------------- 
--      Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  

/* Columns for retrieving CommVault Backups 
   DISTINCT SERVERPROPERTY('Servername') AS SRVNAME 
*/

/* Columns for retrieving information */

   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   -- msdb.dbo.backupset.expiration_date,
   msdb..backupset.type, 
   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],  
   -- msdb.dbo.backupmediafamily.device_type,
   CASE msdb.dbo.backupmediafamily.device_type 
       WHEN 2 THEN 'Disk'
       WHEN 5 THEN 'Tape'
       WHEN 7 THEN 'Virtual Device' 
       WHEN 9 THEN 'Azure Storage' 
       WHEN 105 THEN 'A permanent backup device'
       ELSE 'Unknown' 
   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.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,

   
   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


/* ----------------------------------------------------------------------------
    generic WHERE statement to simplify selection of more WHEREs    
-------------------------------------------------------------------------------*/
WHERE 1 = 1

/* -------------------------------------------------------------------------------
    statements to find Backups saved in standard directories
---------------------------------------------------------------------------------- */
-- AND   physical_device_name  LIKE '[dDhHfF]:%'                        -- STANDARD Backup Directory 
-- AND   msdb.dbo.backupset.is_copy_only != 1                           -- Copy Only Backups
-- AND   physical_device_name  NOT LIKE '[nN]:%'                        -- STANDARD Product  Backup Directory
-- AND   physical_device_name  LIKE '%.%'                               -- STANDARD Backup Directory 
-- AND   physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$'   -- Sharepoint Backs up to Share
-- AND   backupset_name NOT LIKE '%Galaxy%'                             -- CommVault Sympana Backup
-- AND   (msdb.dbo.backupset.is_copy_only = 1 AND msdb.dbo.backupset.is_snapshot = 1) -- ESXi Snapshot Backups


/* -------------------------------------------------------------------------------
    Statement to find backup information for a certain period of time
---------------------------------------------------------------------------------- */
AND   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- n days old or younger
-- AND     (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE() - n)  -- n days old or older


/* -------------------------------------------------------------------------------
    Statement to find backup information for (a) given database(s) 
---------------------------------------------------------------------------------- */
AND     database_name IN ('PMI')                                      -- database names seperated with commas

/* -------------------------------------------------------------------------------
    Statement to find specific backup type information 
---------------------------------------------------------------------------------- */
-- AND     msdb.dbo.backupset.type  = 'D'                               -- full (D), differentail (I) and TLog (L)


/* -------------------------------------------------------------------------------
GROUP BY clause to group for special cases 
------------------------------------------------------------------------------- */

--GROUP BY    msdb.dbo.backupset.database_name 

/* -------------------------------------------------------------------------------
        Order Clause for DISTINCT Statement 
----------------------------------------------------------------------------------
ORDER BY  SRVNAME 
*/

/* -------------------------------------------------------------------------------
        ORDER Clause for other statements
---------------------------------------------------------------------------------- */
ORDER BY   --2,

           2 desc,3 desc;

Como você ainda não elaborou seus detalhes, esta é apenas uma recomendação geral para descobrir se você está possivelmente interferindo na solução de terceiros ou vice-versa. Você veria isso talvez na software_namecoluna.

Você pode ter uma solução de terceiros realizando alguns backups e restaurações de teste engraçados, então dê uma olhada na msdb.dbo.restorehistorytabela. Pode ser até mesmo outro DBA.

select * from msdb.dbo.restorehistory;

Restaurar um banco de dados pode alterar o database_backup_lsnou qualquer outro ....lsndocumentado no histórico de backup. Isso pode, por sua vez, impedir que você execute backups diferenciais adicionais após uma restauração ter ocorrido.

Recomendação: Após uma restauração de banco de dados, sempre execute um backup completo do banco de dados.

Após estudar a saída do seu histórico de backup para o banco de dados PMI, posso fazer uma suposição. Aqui está uma análise codificada por cores dos backups sendo realizados:

_{Por favor, pressione CTRL + clique na imagem para visualizá-la em uma nova aba}

Como você pode ver no exemplo codificado pela cor azul, o Backup FULL tem um first_lsn(lsn = Número de Sequência de Log) de 104219000001355000000. Este é o lsn em que o banco de dados estava quando o backup FULL começou.

Os backups DIFF subsequentes são todos baseados no first_lsndo backup FULL. Você pode ver isso em database_backup_lsne nas differential_base_lsncolunas.

O exemplo codificado pela cor laranja é similar. Há um backup FULL que ocorre em 104219000001374000000e este é o database_backp_lsne differential_base_lsnpara o backup DIFF subsequente.

SUPOSIÇÃO

Como você pode ver nos elementos codificados por cores rosa, eles meio que se destacam. Eles realizam um backup COMPLETO todos os dias às 2h (02:00). No entanto, esse backup COMPLETO não é um backup como o SQL Server faria, é um backup de snapshot. A E/S é congelada nos discos e a solução de terceiros realiza um snapshot dos discos.

Então, alguma solução de terceiros ou seu ambiente de virtualização está realizando um snapshot do servidor. Este snapshot é registrado no msdbhistórico de backup do banco de dados como um backup COMPLETO. Isso ocorre porque ele SQL Server Writer Servicerecebe uma solicitação do SO para congelar todas as E/S. Como o SQL Server está envolvido, ele marca este snapshot como um backup COMPLETO no msdbbanco de dados.

Mesmo sendo um snapshot, ele redefinirá as colunas first_lsne database_backup_lsn.

Vejamos o exemplo rosa superior.

O Snapshot Backup em 2024-11-17 01:59:54.000tem um first_lsnde 104219000001362000000. Este é então o database_backup_lsnpara o segundo snapshot que é executado e para o backup COMPLETO que foi executado por você.

Seu backup FULL não será quebrado, porque ele não precisa depender da presença do backup instantâneo FULL anterior. Ele apenas pega o que tem das páginas no arquivo *.mdf e faz backup disso junto com quaisquer transações confirmadas.

Olhando o histórico de backup, imagino que você esteja tendo problemas ao executar um backup DIFF após um SNAPSHOT.

Por que?

O backup instantâneo não pode ser usado para executar um backup DIFF adicional do banco de dados. Isso ocorre porque o snapshot não é um backup FULL verdadeiro (em termos de SQL Server). Ele é consistente e pode (na maioria dos casos) ser usado para colocar um servidor online novamente, mas não pode ser usado como base para restaurações adicionais (pense em restaurações DIFF e TLOG).

Um backup instantâneo voltará a ficar online e será recuperado. Você não pode parar no meio do caminho (WITH NO_RECOVERY) e então restaurar backups DIFF e/ou TLOG adicionais.

Soluções possíveis

1. Diga à sua solução de terceiros para adicionar a COPY_ONLYopção ao snapshot. Com essa opção, o database_backup_lsnnão é redefinido e sempre apontará para o último backup FULL que não era um backup snapshot. Os backups DIFF subsequentes funcionarão!

2. Depois que a solução de terceiros executar um snapshot, faça com que seu software de backup execute um backup COMPLETO consistente do seu banco de dados.

3. Se você puder definir a opção 1. e estiver realizando backups reais do SQL Server com uma solução de backup, não há necessidade de realizar um backup COMPLETO após a solução de terceiros ter realizado um snapshot. Basta executar um backup DIFF que deve referenciar seu último Backup COMPLETO do SQL Server ( first_lsn) e tudo estará bem.

Informações adicionais sobre instantâneos

_{...com base no comentário: Depois de pensar sobre isso, tenho um snapshot de VM com Proxmox. Eu não sabia que snapshots de VM podiam registrar um backup no SQL Server. Como faço para contornar isso? Quero manter snapshots Proxmox agendados.}

Você não pode parar o automatismo. Isso é explicado no artigo SQL Writer service (Microsoft Learn | SQL).

O serviço SQL Writer fornece funcionalidade adicional para backup e restauração do SQL Server por meio da estrutura do Serviço de Cópias de Sombra de Volume.

O serviço SQL Writer é instalado automaticamente. Ele deve estar em execução quando o aplicativo Volume Shadow Copy Service (VSS) solicitar um backup ou restauração. Para configurar o serviço, use o applet Microsoft Windows Services. O serviço SQL Writer é instalado em todos os sistemas operacionais Windows.

Mais abaixo no artigo você encontrará o propósito desta estrutura:

Objetivo
O Database Engine bloqueia e tem acesso exclusivo aos arquivos do banco de dados. Quando o serviço SQL Writer não está em execução, os programas de backup em execução no Windows não têm acesso aos arquivos de dados, e os backups devem ser realizados usando o backup nativo do SQL Server. Use o serviço SQL Writer para permitir que os programas de backup do Windows copiem os arquivos do banco de dados do SQL Server enquanto o SQL Server estiver em execução e mantendo bloqueios exclusivos nesses arquivos.

...que inclui seu Proxmox Snapshot.

A seguir você tem essas informações:

Volume Shadow Copy Service
O VSS é um conjunto de APIs COM que implementa uma estrutura para permitir que backups de volume sejam realizados enquanto os aplicativos continuam a gravar nesses volumes. O VSS ajuda na coordenação entre aplicativos de usuário que gravam dados no disco (gravadores) e aplicativos que fazem backup desses dados (solicitantes).

^{Referência: Serviço SQL Writer (Microsoft Learn | SQL)}

Em essência, você não pode alterar como o SQL Writer Service auxilia na criação de snapshots e também no registro dos snapshots. É uma parte essencial de como snapshots consistentes são realizados. SE você desativasse o SQL Writer Service, você teria um snapshot Proxmox corrompido .

Uma pergunta semelhante foi feita em Uso de backup VSS de terceiros mais backup SQL nativo (DBA Stackexchange) e também foi respondida por mim.

Minha conclusão foi:

Os backups de terceiros devem ser marcados como is_snapshot = 1e is_copy_only = 1. Esses backups não entrarão em conflito com etapas/procedimentos de backup adicionais realizados usando instruções nativas do SQL Server BACKUP DATABASE..., BACKUP DATABASE ... WITH DIFFERENTIAL.... e BACKUP LOG.... Os backups de banco de dados de terceiros não fazem parte de um conjunto de backup existente.

Mais informações sobre o VSS e o SQL Writer Service foram fornecidas por mim na minha resposta sobre Como posso fazer backup de um banco de dados SQL Server usando o Windows Server Backup? (Serverfault).