Issues with SQL Database Log Backups

 I have 1 Production Server X, database Auditdb, Enterprise edition, 2014

Full backups run 12am every night(NO Differential)

T log run every 10m minutes, Today someone updated a big table without where clause and i had to restore that data, so I tried do in our UAT Server first and then do a compare or update manually...

so, in UAT when I tried to restore: 12:01:39am Full backup with NO RECOVERY --good

then started to restore all T log backups  starting from 12:10:11am, bases on scripts attached , i was able to to restore with 2 t logs with NO RECOVERY ,but failed on 3 rd t log file..

  i have attached  the error which i got , i tried to look at LSN , file names based on date and did lot of research but no luck,

can someone please take a look at it let me know what im i doing wrong?

and please provide any working script  to restore T logs in order ? 

how can i know the next T log file to restore?after Full, tlog1, tlog2 like that?

you know what i found: 

We have Alwayson Availability groups : Primary replica and  Secondary replica and i have FULL(12am daily)and Log backup(every 10mins) jobs running On primary and secondary replica after checking IF PRIMARY THEN BACKUP using maintenance plan, ELSE EXIT...

So something is happening at 12:31AM everyday on secondary ,  T log backup is being taken using NT Authority/System and also the name of the File is NULL . I looked at  all jobs  to see if there are any T-logs  but found no jobs running. I am going to run a trace to find what NT Authority/System  is doing between 12-13:45 am and also i disabled T-log backup job which i mentioned above on secondary.

Note:

Primary was always primary and no failover happened 

We have Veeam backups which kick start at 1am which does file copy not SQL style backups

No Scheduled Jobs in OS or SQL which could kick start T-log other than mine.

this issue is happening Everyday at same time on secondary replica for all databases 

    
SELECT   top 10 
        [b].[backup_start_date] ,
        [b].[backup_finish_date] ,
        [b].[type] ,
        [b].[first_lsn] ,
        [b].[last_lsn] 
FROM    [msdb].[dbo].[backupset] AS [b] 
     where[b].[backup_start_date] >= '2016-08-23 00:01:00' and database_name like '%Auditdb%'
ORDER BY [b].[backup_start_date];

 backup_start_date backup_finish_date type first_lsn last_lsn
2016-08-23 00:01:18.000 2016-08-23 00:05:06.000 D 13983000002259000001 13983000002262100001
2016-08-23 00:10:07.000 2016-08-23 00:10:07.000 L 13983000002259000001 13983000002264600001
2016-08-23 00:20:14.000 2016-08-23 00:20:14.000 L 13983000002264600001 13983000002265500001
2016-08-23 00:30:05.000 2016-08-23 00:30:05.000 L 13983000002265500001 13983000002266400001
2016-08-23 00:40:05.000 2016-08-23 00:40:05.000 L 13983000002267000001 13983000002268000001
2016-08-23 00:50:06.000 2016-08-23 00:50:06.000 L 13983000002268000001 13983000002268900001
2016-08-23 01:00:06.000 2016-08-23 01:00:06.000 L 13983000002268900001 13983000002287100001
2016-08-23 01:05:05.000 2016-08-23 01:05:05.000 L 13983000002287100001 13983000002290400001
2016-08-23 01:10:05.000 2016-08-23 01:10:05.000 L 13983000002290400001 13983000002291300001
2016-08-23 01:20:05.000 2016-08-23 01:20:05.000 L 13983000002291300001 13983000002292200001

 SECONDARY:

backup_start_date backup_finish_date type first_lsn last_lsn
2016-08-23 00:33:24.000 2016-08-23 00:33:24.000 L 13983000002266400001 13983000002267000001

THEN ::::::::

I found why LSN Chain in SQL Server Database backups is breaking on FPMC at 12:30am and on FPVI at 1am Daily.

On FPMC: 12:30am

I looked at SQL Profiler Trace data which is happening with Login name “NT AUTHORITY\SYSTEM” and  found Veeam Endpoint Backup is running  

       DECLARE @database_name_var NVARCHAR(255) SET @database_name_var = N'AuditDB' BACKUP LOG @database_name_var TO DISK = 'NUL' .

This is happening against all databases, and TRAUNCATING LOG , Breaking LSN (Backup to Nul is TRASH- No location), and should be avoided Immediately in order to have a Point in time recovery.

As per my googling  I found that Veeam does by default backup the log to the filename "nul" before it takes the VSS snapshot

We need to make some modification to Veeam Endpoint Backup and remove the step which is doing back log to nul which we don’t need,  and same thing happening on FPVI-SQL1HA at 1am

https://helpcenter.veeam.com/backup/howtosql/how2_sql_choose_bu_tool.html

https://forums.veeam.com/veeam-endpoint-backup-f33/endpoint-backup-ms-sql-backup-t27878-15.html

Fyi: Veeam Endpoint Backup is Scheduled to run Backups on FPMC at 12:30am and on FPVI at 1am Daily.

I added a (32 bit) DWORD – VSSForceOnlyCopy=1 to the following Registry location on both SQL HA Nodes. – HKLM\SOFTWARE\VEEAM\VEAM ENDPOINT BACKUP

From what I read, that should fix the issue.