Como saber se uma cadeia de logs de backup está quebrada?

Leitura de referência / Perguntas e respostas semelhantes

Você pode querer verificar minha resposta que postei em resposta à pergunta: Os backups do VSS quebrarão o logchain? (dba.stackexchange.com)

A explicação na minha resposta também está vinculada à pergunta Como posso fazer backup de um banco de dados do SQL Server usando o Backup do Windows Server? (serverfault.com) que também foi respondido por mim.

Cadeia de log de transações

Quando um backup de log de transações (TLOG) é executado, as informações de backup são armazenadas no banco de dados msdb em várias tabelas. As informações armazenadas conterão informações como backup_type, logical_device_name, physical_device_name, is_copy_only, is_snapshote várias ..._lsncolunas (lsn = número de sequência de log).

Você pode recuperar as informações da cadeia de backup do log de transações de sua instância do SQL Server por meio do banco de dados msdb com o seguinte script:

/* ==================================================================
 Author......:  hot2use 
 Date........:  25.04.2018
 Version.....:  0.1
 Server......:  localhost (first created for)
 Database....:  msdb
 Owner.......:  -
 Table.......:  various
 Type........:  Script
 Name........:  ADMIN_Retrieve_Backup_History_Information.sql
 Description.:  Retrieve backup history information from msdb database
 ............   
 ............   
 ............       
 History.....:   0.1    h2u First created
 ............       
 ............       
================================================================== */
SELECT /* 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, 
       
       CASE msdb.dbo.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.logical_device_name,
       msdb.dbo.backupmediafamily.physical_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.checkpoint_lsn,
       msdb.dbo.backupset.database_backup_lsn,
       msdb.dbo.backupset.differential_base_lsn,
       msdb.dbo.backupset.first_lsn,
       msdb.dbo.backupset.fork_point_lsn,
       msdb.dbo.backupset.last_lsn
FROM   msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
            ON  msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
                
        /* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
        -------------------------------------------------------------------------------*/
WHERE  1 = 1
       
       /* ----------------------------------------------------------------------------
       WHERE statement to find Device Backups with '{' and date n days back
       ------------------------------------------------------------------------------- */
       -- AND     physical_device_name LIKE '{%'
       
       /* -------------------------------------------------------------------------------
       WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b 
       ---------------------------------------------------------------------------------- */
       -- AND     physical_device_name  LIKE '[fF]:%'                          -- STANDARD F: Backup Directory
       -- AND     physical_device_name  NOT LIKE '[nN]:%'                      -- STANDARD N: Backup Directory
       
       -- AND     physical_device_name  NOT LIKE '{%'                          -- Outstanding Analysis
       -- AND     physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
       -- AND     backupset_name NOT LIKE '%Galaxy%'                           -- CommVault Sympana Backup
       
       
       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b 
       ---------------------------------------------------------------------------------- 
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- 7 days old or younger
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE())  -- n days old or older
       
       */
       
       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for (a) given database(s) 
       ---------------------------------------------------------------------------------- */
       AND database_name IN ('AdventureWorks2012') -- database names
       -- AND     database_name IN ('rtc')  -- database names
           
        /* -------------------------------------------------------------------------------
        ORDER Clause for other statements
        ---------------------------------------------------------------------------------- */
        --ORDER BY        msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause
           
        ---WHERE msdb..backupset.type = 'I' OR  msdb..backupset.type = 'D'
ORDER BY
       --2,
       
       2       DESC,
       3       DESC 

^{Cuidado: A cláusula where atualmente seleciona o banco de dados AdventureWorks2012}

Cadeia de log de transações quebrada

A cadeia de log (transação) nunca é interrompida, a menos que uma das seguintes condições seja atendida:

• um arquivo de backup do log de transações foi excluído
• um arquivo de backup do log de transações não está acessível (em algum lugar em um dispositivo de backup; solução de backup de terceiros)
• o banco de dados está no modelo de recuperação SIMPLES
• um backup do log de transações foi realizado com a opçãoTRUNCATE_ONLY

Sua situação

Na captura de tela que você forneceu você tem um FULLbackup do banco de dados que é is_copy_onlye logo após um FULLbackup que não is_copy_only é . Agora o que você não sabe:

O segundo FULL, sem is_copy_onlybackup, é um instantâneo ou não?

Se você usar meu script acima e alterar a WHEREcláusula para corresponder ao nome do seu banco de dados, poderá descobrir que esse FULLbackup que não é is_copy_onlypode ser apenas um arquivo is_snapshot.

E isso pode abrir uma nova pergunta:

O backup FULLde is_snapshotbanco de dados do meu banco de dados interromperá a cadeia de backup de log?

Mas...

.... seja qual for o caminho, desde que você tenha uma cadeia ininterrupta de FULLbackups TLOGque possa acessar, você ainda poderá restaurar seu banco de dados para qualquer ponto no tempo, mesmo se tiver outro FULLbackup no meio.

Você pode verificar isso com a saída do meu script para seu banco de dados, observando os números first_lsne . last_lsnEles devem corresponder, mesmo ao ignorar um FULLbackup.

Melhor prevenir do que remediar

Eu tenho um AdminDB2banco de dados em uma das minhas instâncias. Criei um TLOGbackup, modifiquei dados, fiz backup, modifiquei dados FULL, fiz TLOGbackup, ....

Vamos dar uma olhada no meu histórico de backup do meu AdminDB2:

dbname    backup_start_date       backup_finish_date            type    Log   physical_device_name                                          C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-25 17:29:08.000 2018-04-25 17:29:08.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172908.trn   0   0   36000002022400042   36000002022400042   NULL    36000002021900001   NULL    36000002025100001
AdminDB2    2018-04-25 17:28:48.000 2018-04-25 17:28:48.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172848.bak   0   0   36000002022400042   36000002018900037   NULL    36000002022400042   NULL    36000002024200001
AdminDB2    2018-04-25 17:28:23.000 2018-04-25 17:28:23.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172823.trn   0   0   36000002018900037   36000002018900037   NULL    36000002021500001   NULL    36000002021900001
AdminDB2    2018-04-25 17:28:07.000 2018-04-25 17:28:07.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172807.trn   0   0   36000002018900037   36000002018900037   NULL    36000002018400001   NULL    36000002021500001
AdminDB2    2018-04-25 17:27:32.000 2018-04-25 17:27:32.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak   0   0   36000002018900037   36000001990800037   NULL    36000002018900037   NULL    36000002020600001
AdminDB2    2018-04-25 17:15:00.000 2018-04-25 17:15:00.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_171500.trn   0   0   36000002016000003   36000001990800037   NULL    36000002018100001   NULL    36000002018400001

_{A ordem é decrescente de data}

You can see the last TLOG backup at the top, the previous FULL (in-between) backup at 2018-04-25 17:28:48.000, the previous TLOG backup at 2018-04-25 17:28:23.000, and so on.

To restore the AdminDB2 database to the current point-in-time I would have to use the first FULL backup from 2018-04-25 17:27:32.000 (because I deleted the in-between FULL backup) together with all the TLOG backups.

Let's give that a try.

1. Delete the FULL backup file AdminDB2_FULL_20180425_172848.bak on my disk (or rename it), just because it is the one in-between.
2. Open up the Restore GUI in SSMS and add ..
   • the FULL backup AdminDB2_FULL_20180425_172732.bak
   • all the TLOG backup files
     • AdminDB2_TLOG_20180425_172807.trn
     • AdminDB2_TLOG_20180425_172823.trn
     • AdminDB2_TLOG_20180425_172908.trn
3. Make sure i set the option Overwrite the existing database (WITH REPLACE)
4. Perform the restore (or script the statement out into a query window)

Script

USE [master]
RESTORE DATABASE [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5, REPLACE
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172807.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172823.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172908.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Output

15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
100 percent processed.
Processed 848 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE DATABASE successfully processed 850 pages in 0.134 seconds (49.502 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.027 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.005 seconds (0.390 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.125 MB/sec).

...and the database is back ONLINE.

Summary

The backup chain only breaks when you lose the TLOG backups in-between, other than that you can restore a database from a FULL backup a long time ago and just add all the TLOG backups.

However...

...it is faster to have a more recent FULL, DIFF and TLOG backups handy.

_{Additional information in response to comment: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query}

Backing Up Transaction Log With Truncate_only

In previous versions of SQL Server prior to SQL Server 2008 you could use the following statement:

 BACKUP LOG [AdminDB2] WITH TRUNCATE_ONLY

This has been deprecated and is no longer supported. You will receive an error message like the following:

Msg 155, Level 15, State 1, Line 10
'TRUNCATE_ONLY' is not a recognized BACKUP option.

The new method is to backup to disk NUL and is performed with the following command:

BACKUP LOG [AdminDB2] TO DISK='NUL'

This will return the following information:

Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.001 seconds (1.464 MB/sec).

Advisory
Do NOT use this in production. You will lose the ability to restore to a point-in-time during that TLOG backup.

Reference: BACKUP (Transact-SQL) (Microsoft Docs)

Your backup history will show this as:

dbname      backup_start_date       backup_finish_date      type ldev  pdev C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-26 09:35:05.000 2018-04-26 09:35:05.000 Log NULL    NUL 0   0   36000002030100002   36000002022400042   NULL    36000002033400001   NULL    36000002033700001

The information for the logical_device_name (ldev) and physical_device_name (pdev) will both contain the value NULL. This is a sign that a BACKUP LOG ... was performed with a TRUNCATE_ONLY (new: TO DISK='NUL'). You will have lost the ability to restore past this point using Transaction Log backups.

_{Additional information in response to comment: Yes - this was a is_snapshot = 1 [backup]}

is_snapshot

Please read the section is_snapshot in my answer to the question Use of third-party VSS backup plus native SQL backup

From my original answer:

If the database backup history has the flag is_snapshot set to 1 then you know that this backup was performed using a 3rd-party software that triggered the SQL Server Writer (VSS Service for SQL Server) which allowed the 3rd-party software to backup the database almost instantaneously.

From the official documentation on what Snapshot Backups are:

SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

Reference: Snapshot Backups (Microsoft Technet)

A backup created using this feature can also be restored almost instantaneously.

Summary

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 executados usando SQL Server nativo BACKUP DATABASE...e BACKUP DATABASE ... WITH DIFFERENTIAL....instruções BACKUP LOG.... Os backups de banco de dados de terceiros não fazem parte de um conjunto de backup existente.

Espero que esta informação seja suficiente.

De acordo com a documentação do MSDN BACKUP DO LOG DE TRANSAÇÕES e SEQUÊNCIA DE RESTAURAÇÃO: Mitos e verdades Uma sequência contínua de T-Logbackups é vinculada por um Log Chain, que começa com um backup COMPLETO. Agora, a menos que executemos qualquer coisa explicitamente que quebre a cadeia de logs (Ex., executando BACKUP log TRUNCATE_ONLY* ou alternando para o modelo de recuperação SIMPLE), a cadeia existente permanece intacta. Com a cadeia de logs intacta, você pode restaurar seu banco de dados a partir de qualquer backup COMPLETO de banco de dados no conjunto de mídia, seguido por todos os T-Logbackups subsequentes até o ponto de falha.

E como MSSQLTIPSdocumentos aqui Ao restaurar um banco de dados, a sequência RESTORE inicial do banco de dados deve começar a partir de um backup COMPLETO do banco de dados. Uma sequência RESTORE de banco de dados não pode começar com um backup de arquivo diferencial ou backup de log de transações. Ao restaurar bancos de dados, existem quatro LSNs importantes: FirstLSN, LastLSN, CheckpointLSNe DatabaseBackupLSN. Esses valores podem ser recuperados de um arquivo de backup do SQL Server usando o comando RESTORE HEADERONLY .

Por exemplo

Na captura de tela acima, quero mostrar o cabeçalho "Backup completo" e também o cabeçalho "Backup do log de transações". Se o tipo de backup for 1 , o que significa que é uma parte do cabeçalho do backup completo. E se houver 2 , isso significa que é o cabeçalho de backup do log de transações.

Nesta captura de tela, quero mostrar primeiro Restore Headeronly.. para Backup completo, em seguida, backup do log de transações e novamente backup completo do mesmo banco de dados.

Nota: Aqui eu destaquei alguma parte na captura de tela por motivos de segurança.

Para sua referência adicional aqui e aqui

Depois de ler sua pergunta, não estou convencido de que sua "cadeia de logs" esteja quebrada por causa desse Appsurebackup. Supondo que você possa restaurar o FULLbackup feito APPSUREna linha 5 WITH NORECOVERY, você poderá restaurar o DIFFERENTIALbackup feito na linha 6 sem problemas.

Acredito que sua verdadeira pergunta seja:

Como posso determinar se backups não copiados 'não autorizados' estão sendo feitos sem meu conhecimento .FULL

Pode haver maneiras mais sofisticadas de determinar isso, mas talvez uma consulta simples para verificar se os backups não somente cópia estão armazenados em um local que você não esperava seria suficiente.

Pela captura de tela, parece que seus backups normais estão sendo armazenados em E:\SQLBackups. Pode ser suficiente executar uma consulta simples para verificar FULLse os backups que não são somente cópia estão armazenados em outro lugar.

SELECT s.database_name
    ,m.physical_device_name
    ,s.backup_start_date
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
    AND s.is_copy_only = 0
    and physical_device_name not like 'E:\SQLBackup%'
ORDER BY backup_start_date DESC