SQL Server Transaction Log Shipping does not restore .TRN files

Recently a customer was concerned to implement a fail over strategy for the Dynamics AX database. SQL Server 2008 R2 Standard Edition was in place. The underlying hardware was not capable to handle database mirroring. Therefore, log shipping and manual failover was chosen. However, the job on the second server refused to restore the .trn files. We managed to reproduced the problem in our lab environment with two SQL Instances (SQL1, SQL2) and a Northwind Demo Database.

 

Log Shipping Configuration

There are 2 SQL Servers in place, called SQL1 (primary) hosting a Northwind Database and SQL2 (traget). There are two file shares on the secondary server. LSBackup is used to backup .trn files from the primary server. LSCopy is used by the secondary server to copy .trn files and restore these files to the replicated database.

image

The backup at the primary server SQL1 was scheduled to run every 5 minutes. Files older than 1 hour in the LSBackup folder will be deleted.

image

The target database at the secondary server SQL2 was initialized by the configuration wizard

image

The copy job at the secondary server SQL2 was scheduled to run every 5 minutes. This job copies .trn files from the LSBackup folder into the LSCopy folder. Files older than 1 hour in the LSCopy folder will be deleted.

image

The restore job at the secondary server SQL2 was scheduled to run every 5 minutes.

image

 

Log Shipping Behavior

The log shipping configuration seemed to be correct. The backup job on the primary server SQL1 placed the backup and .trn log backup files in the LSBackup folder. The copy job on the secondary server copied the .trn files to the LSCopy folder.

image

An initial check of the restore job history showed no errors.

image

 

The Problem

A detailed look at the job revealed that the .trn files were not restored. The backup job reported “Skipped log backup file” because it “Could not find a log backup file that could be applied to the secondary database”"

image

 

The Reason

A maintenance plan was in place on the primary server SQL1. This maintenance plan took a full backup at midnight and a transaction log backup every 15 minutes. The interleaving transaction log backups resulted in holes within the transaction log chain. Therefore the restore job on SQL2 was not able to apply the copied transaction log files.

image 

 

The Solution

At the Northwind DB properties on primary server SQL1, the transaction log replication setting to SQL2 was removed. The wizard also removed the copy and restore jobs at SQL2. The replicated Northwind DB at SQL2 was removed manually. The transaction log shipping configuration was deactivated at the Northwind DB on SQL1.  All files in the LSBackup and LSRestore folders were deleted. The maintenance plan was deactivated.

Next the transaction log shipping configuration was redone, as described above. Again the target database at SQL2 was initialized by a full backup of the Northwind DB from SQL1. Finally the secondary server SQL2 was able to restore the .trn files.

image

Advertisements

Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: