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,NU...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?
If the log backups are not in native format, then Replicate will not be able to read them.
If the backup was done viacommvault, 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.
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
