Qlik Replicate task fails after a SQL Server Database Restore

[lyka]

A task fails after a database restore.

Example errors:

2022-03-22T01:05:27 [SOURCE_CAPTURE ]E: Failure validating initial LSN '001f2fb0:000153a8:0009' 'anywhere existance'. Found nowhere. [1020101] (sqlserver_log_utils.c:8505)

Indicates: Qlik Replicate cannot find the LSN in the online database or in any backup.

00145864: 2022-02-07T18:16:53:496172 [SOURCE_CAPTURE ]E: SqlStat: 42000 NativeError:3231 [Microsoft][SQL Server Native Client 11.0][SQL Server]The media loaded on "\\xxxx\SQLBakFiles$\DocPrep\DocPrep_backup_2022_02_05_150300_30.trn" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (PcbMsg: 276) [1020417]

Indicates: There may be two backups listed per LSN range and a multi-thread backup is performed.

Resolution

When restoring the database, you need to clean up the MSDB and ensure that the following query will only return one row:

select
[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),
bmf.* , bs.*
from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs
where bmf.media_set_id = bs.media_set_id
and bs.database_name=db_name() and bs.type='L'
and cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar (24) ) <=cast( '0020811f:0000474f:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar (24) )
and cast( '0020811f:0000474f:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar (24) ) < cast( [dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar (24) );

Note: Replace the LSN with the correct LSN from the log file.

Here are commands you can use to delete the backup history:

sp_delete_database_backuphistory [ @database_name = ] 'database_name'
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

Environment

Qlik Replicate