I have configured a log shipping for our company's primary database to restore on a separate server, with the restored database serving as report database.
The problem is that every few days, the log shipping breaks and unfortunately, more often than not, a full backup happens after the break which prevents me from doing a differential restore to "un-break" the LSN chain. Here are the results of the LSN chain for your reference (primary server and database):
I can usually restart the LSN chain if I can do differential restore before the full backup happens but I should not really depend on that. The main issue here is I am getting a "NUL" message in the following query, and I am 100% sure that the LSN breaks where the "NUL" happens, as the last restored log is the one with the green highlight. I would really appreciate anyone's input. Thanks.
DECLARE @db_name VARCHAR(100) SELECT @db_name = 'BESIDE' SELECT TOP (30) s.database_name ,m.physical_device_name ,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize ,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken ,s.backup_start_date ,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn ,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn ,CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType ,s.server_name ,s.recovery_model 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 ORDER BY backup_start_date DESC ,backup_finish_date