No FULL database backup found and MS SQL Server instance not set up for Replication errors

[JimLittle]

I have a Qlik Replicate task where the source database is SQL Server with HA.  The task is pointed to the primary node, and the backups are taken from the secondary.  We have registered the incremental backups with the primary MSDB, and that is working with no issue.  The source server is using MS Replication and has had a FULL backup taken.

Last night the task restarted, and near the top of the newly created log file, are these statements:

I: Ignore_Ms_Replication_Enablement is internally set to 'false' (sqlserver_log_queries.c:3098)
W: The MS SQL Server instance is not set up for Replication. (sqlserver_log_queries.c:3118)
I: Access to TLOG is permitted. (sqlserver_log_queries.c:3239)
I: Database 'FULL' recovery model fitness approved. (sqlserver_log_queries.c:3277)
E: No FULL database backup found (under the 'FULL' recovery model). To enable all changes to be captured, you must perform a full database backup. [1020438] (sqlserver_log_queries.c:3336)

Why is it that the task is reporting No FULL database backup can be found, and MS Replication is not set up?  I absolutely can confirm that a FULL backup was taken an hour prior, and MS Replication is configured?

Note: our incremental backups have been registered, but not the FULL backup, which is in a different location.  Is the error reporting that it can't find the file?

Thanks,

Jim

[SachinB]

Hello @JimLittle ,

This is the underlying reason for the "No FULL database backup found" message. It indicates that Replicate knows then MS SQL is not delivering reliable change data and disables CDC.

To prepare for backup and recovery you must make sure that the Microsoft SQL Server Recovery Model is set up. You select the Recovery Model in the Microsoft SQL Server Management Studio. This should be carried out by a Microsoft SQL Server system administrator.

The TLOG data is truncated as soon as it is no longer needed therefore the TLOG is not persistent. However, Qlik Replicate guaranteed delivery requires persistency in the changed data. To ensure persistency:

A full database backup must be carried out before beginning to replicate data.
The Recovery Model must be set to Bulk logged or Full.

Please follow the below link for the same.

Preparing Microsoft SQL Server backup and recovery #Preparing Microsoft SQL Server backup and recove...

Regards,

Sachin B

[DesmondWOO]

Hi @JimLittle ,

Please try following steps to perform full backup in the Microsoft SQL Management Studio:

Right click your database -- Task -- Back up -- select "Full" for the backup type 

If you still encounter "No FULL database backup found" message.", please run following SQL statement to check:

SELECT s.database_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() and s.[type]='D'
and s.recovery_model in ('FULL','BULK-LOGGED');

If full backup is performed, it should return a record for your database.

Regards,
Desmond

[Heinvandenheuvel]

Checking for full-backup is done out of self-preservation by Replicate because potentially SQL server itself can choose NOT to update the Tlog when it knows there is not main backup to apply to. For Replicate the main backup is irrelevant. Therefor if everything else is in place corrcetly (Tlog backups) then one can request a 'quick' backup to NUL to satisfy the Replicate check and move on.

Hein.