No FULL database backup found

[bihluh]

Hi Support,

I am getting this error when setting up Qlik replicate:

No FULL database backup found.

My Source is a SQL Server AlwaysOn  Availability Group and the database backup is running on secondary node. 

The full load seems working but all the subsequence data changes are not being replicate to the target server.

Regards,

Bih Luh

[PGN]

If you're running backups on the secondary node, then the primary node's MSDB needs to have those entries so that QLIK Replicate can validate that a Full and subsequent tlog backups have run.  This can be accomplished by using a Powershell script or other means to copy the entries from the secondary to the primary node.   You don't say if you're using TLOGs to identify changed data, if that's the case, you would also need to copy those entries so that Replicate can find the log files if needed.  

Regarding the second part of your question, I'm not sure what you're experiencing.  Are the tasks set up to Apply Changes?  If so, have the full loads completed?

[Heinvandenheuvel]

When you made no full back ever, MS SQLServer will NOT create a Tlog backups. It only uses the Tlog for transactional consistency, not for availability and recycles space based on that. It reasons there is no base backup to apply the Tlogs backup so why bother! It does not anticipate other (Replicate) desires to read what is going on.

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. Please be sure to search the Replicate User Guide for "FULL database".

The basic fix for that is easy. Take a full backup! You can walk away from it when done, and you can even direct the backup to the bit-bucket: NUL

I'm not sure how this plays out in the AlwaysOn environment, but that's where I'd start.

Hein.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql...

[PGN]

He stated that he's running backups on the secondary node.  As such, the primary node's MSDB, which cannot be included as part of AOG, isn't aware of the full backup being done.  For Replicate to know the full backup is done, or to keep track of TLOG backup location (if he's using TLOGS in Replicate to track changes), then the entries need to be copied to the primary node.  That's how it plays out in an AlwaysOn environment.

[Heinvandenheuvel]

@PGN Yup. Thanks, I know. This is all fairly well documented in the Replicate Userguide.

@bihluh Please Check the User Guide for "Working with Microsoft SQL Server AlwaysOn Availability Groups"

Hein. 

[bihluh]

hi @Heinvandenheuvel , yes, I checked the "Working with Microsoft SQL Server AlwaysOn Availability Groups", but it doesn't advise what happen if the Full backup is running on secondary node. 

We've found the root cause, it was not caused by the Full backup on secondary node. It was due to LOB length limitation. It works well after we change the setting.

It still flagging out as an error, but looks like we can safely ignore it.

Bih Luh

[rchurchagundiamarnat]

@bihluh - what was the LOB length setting before and after ? would you mind sharing that if you can ? Thanks!

[Megha_More]

Full database back up is required when you take SQL server as a source.

So for that just simply right click on db -> task -> back up. There you can see the details , so make sure recovery model and recovery model must be "Full". Add back up destination. -> Ok.

After that reload the task. Hope this will be helpful.