topic Re: Testing Backups in Qlik Replicate

Testing Backups

mbjohnson123 — Tue, 23 Feb 2021 20:12:54 GMT

Is there a way to test that the sql full backup and the log backups are good/readable via sql script? We use a third party vendor (CommVault) and have "select virtual device types" checked in the advanced tab on sql server source connection.

I know that you can run a query like this to test read for the log backup:

select top 1000 [Current LSN], [operation], [Context], [Transaction ID], [Transaction Name], [Begin Time], [End Time], [Flag Bits], [PartitionID], [Page ID], [Slot ID], [RowLog Contents 0], [Log Record], [RowLog Contents 1] from sys.fn_dump_dblog (NULL, NULL,NULL, 1,'R:\Logs\zzsqlcmmcltr$PCMM\CMM2\LOG\zzsqlcmmcltr$PCMM_CMM2_LOG_20200818_001501.trn',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) where ( ( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') ) or ( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW') and ( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX')) and [PartitionID] in (72058209896038400) ) or ([operation] = 'LOP_HOBT_DDL') )

but in our case I can't put a path in the example above for the logbackup read test because instead of path, we get a GUID such as "91baa3f0-7f11-4448-b1dc-548c80fa33a2" in our sql squery for physical device_name.

I would like to run sql scripts before creating the tasks to validate the backups are in place and readable. Any idea of how to make this work?

Re: Testing Backups

lyka — Tue, 23 Mar 2021 17:29:43 GMT

Hello,

If the log backups are not in native format, then Replicate will not be able to read them.

If the backup was done via commvault, then Replicate will not be able to read them as from what i know commvault backups are not in sql native format (unless this has been changed)

When you restore the file thru commvault, the physical file name must match the physical device name in MSDB.

Thanks,

Lyka

Re: Testing Backups

mbjohnson123 — Tue, 23 Mar 2021 17:32:36 GMT

Thanks - Do the full backups have to be native, or can they be commvault?
Qlik doesn't read those directly, right?

Re: Testing Backups

lyka — Tue, 23 Mar 2021 17:44:03 GMT

Hello,

The backup logs has to be in native format. If using Commvault then you have to configure Replicate to use Online Logs Only and sp_repldone for TLOG truncation prevention

Thanks,

Lyka

Re: Testing Backups

mbjohnson123 — Tue, 23 Mar 2021 17:47:36 GMT

Sorry, what I mean is do the full backups also have to be native, or can
those be commvault as long as the log backups are native?