June 16, 2016 at 8:57 am
I have a Log Shipping config between two SQL Server 2014 Standard Edition instances that periodically has the following error.
"The backup data at the end of "\\Path\.trn" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets."
The transaction log backups after this all restore fine. When I run RESTORE VERIFYONLY the result says the backup set on file 1 is valid. This is true when I run it against the file sitting on the primary and on the secondary. Since the subsequent transaction log backups are completing without error and the database goes into the StandBy/ReadOnly state as expected, do I need to concern myself with this periodic error?
June 20, 2016 at 9:12 am
If the log shipping is staying in sync, the errors you are seeing may not be cause for alarm. When you run these queries on the primary/secondary, does everything show in sync?:
-------------------
run on prim:
select lsmp.primary_server,lsmp.Primary_database,lsps.secondary_server,lsps.secondary_database,
lsmp.backup_threshold [Backup Threshold in Mins],lspd.backup_directory,
lspd.backup_share,lsmp.last_backup_file [Latest file backed up],lspd.backup_retention_period [backup retention period],lspd.monitor_server,
lsmp.last_backup_date [Last Backup Date],lsmp.history_retention_period [History Retention Period in Mins]
from
msdb.dbo.log_shipping_primary_secondaries lsps join
msdb.dbo.log_shipping_monitor_primary lsmp on
lsps.primary_id = lsmp.primary_id
Join
msdb.dbo.log_shipping_primary_databases lspd
on
lsps.primary_id = lspd.primary_id
---------------------------------------------------------
run on sec:
select lss.primary_server,lsms.secondary_server,lss.primary_database,lsms.secondary_database,
lss.backup_source_directory,lss.backup_destination_directory,
lss.file_retention_period [backup file retention period on disk in mins],lss.last_copied_file,lss.last_copied_date,
lssd.restore_delay [Delay time set for resore (Mins)],lssd.disconnect_users [Dissconnect users while restore],
lsms.restore_threshold [Restore threshold in Mins],
lsms.last_copied_file,lsms.last_copied_date,lsms.last_restored_file,lsms.last_restored_date,DATEDIFF(MINUTE,lsms.last_restored_date,getdate()) [Restoration Not happened from (Mins)]
from
msdb.dbo.log_shipping_secondary lss join
msdb.dbo.log_shipping_secondary_databases lssd
on lss.secondary_id = lssd.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lsms
on lss.secondary_id = lsms.secondary_id
June 20, 2016 at 10:16 am
Everything is in sync. I can tell that from the Log Shipping Report in the Performance Dashboard Reports. I did run your queries, which show essentially the same info as the built in reports in SSMS.
The error just doesn't make sense to me given that it then goes on and restores the other transaction logs. I would have expected the process to stop once it hit the restore error.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply