September 16, 2011 at 7:33 am
I have been trying to get log shipping set up on a database of mine and it is consistently failing. I initialize the log shipping and the restore job on the standby server works the first time, but then fails every time after that. The logs backup job runs fine, and the copy job to the standby server runs fine every time. Once the restore job runs however it fails after the first time. I have tried completely removing the log shipping and starting over from scratch a number of times, but for some reason, this same behavior happens every time. I have been pulling my hair out trying to figure out what is going on here, to no avail. I have gone through most of the forums available on this topic but with no success. I definitely know that no backups jobs are running on the database as I created it and setup log shipping immediately.
Note: I found that the 1st restore was performed under NT authority\System account even though the SQL agent is setup under my domain account.
Please help folks!
Log Shipping Details:
SQL server and agent are setup and running.
Backup job ships to \\secondaryserver\LS_BackupShare_2005
Logs ship every 15 minutes, alert after 60 minutes, delete older than 3 days
Monitor server(which is primary server) runs every 2 minutes and erases history after 4 days
Standby server copies to C:\test\LS_Copy_2005 every 15 minutes and deletes files older than 3 days
Standby server restores in no recovery mode every 15 minutes, delay of 0 minutes and alert after 45 minutes.
Looking at the tlog shipping report, I can see that the last restored file is actually the first file that was copied. The last copied file is the most recent backup. It hasn't restored a single TRN since the first one that was copied over, every attempt since then has failed. Also the log shipping report on the primary/monitor server shows the status as even when the restore job is failing on the secondary. TLog status report on secondary displays the alert that the restore dint happen for the amount of time.
Below is the full error message that displays in the job history for the restore job on the standby server.
Message
2011-09-16 09:15:01.53Skipped log backup file. Secondary DB: 'TestDB1_RPT', File: 'C:\test\LS_Copy\TestDB1_20110916052001.trn'
It does the same thing for all the available trn files except for the 1st one.
And finally shows success displaying the message,
2011-09-16 09:15:10.59Skipped log backup file. Secondary DB: 'TestVamsiDB1_RPT', File: 'C:\test\LS_Copy\TestDB1_20110916131001.trn'
2011-09-16 09:15:10.62Could not find a log backup file that could be applied to secondary database 'TestDB1_RPT'.
2011-09-16 09:15:10.64The restore operation was successful. Secondary Database: 'TestDB1_RPT', Number of log backup files restored: 0
2011-09-16 09:15:10.66Deleting old log backup files. Primary Database: 'TestDB1'
2011-09-16 09:15:10.68The restore operation was successful. Secondary ID: '50424f33-64ea-471e-be74-c1d2c1a0f284'
2011-09-16 09:15:10.76----- END OF TRANSACTION LOG RESTORE -----
Exit Status: 0 (Success)
Executed as user: Primary server\SYSTEM. The step succeeded.
The job succeeded. The Job was invoked by Schedule 130 (DefaultRestoreJobSchedule). The last step to run was step 1 (Log shipping restore log job step.).
September 16, 2011 at 9:59 am
i usually make the destination folder for the log shipping logs
in a dr location so in case you lose connection they are there
did you check to see if logs are getting copied there
also make sure you remove the database from the maintanence plan job otherwise log shipping gets confused
September 16, 2011 at 10:50 am
Thanks for your reply.
All the logs are being copied appropriately. Its only the restore job is not able to restore the logs and skips. I dont have any maintainence plans setup. I backup the master and msdb databases though. Does this matter?
Also, is there a way to know what all backup jobs are running on a particular database.
September 16, 2011 at 12:00 pm
Is your SQL Agent domain account in local admin group of the secondary server?
September 16, 2011 at 1:47 pm
How can I check that?
September 16, 2011 at 1:54 pm
Follow this path and see if the user that the service is running under is in this group:
-start
-administrative tools
-computer management
-Local Users and Groups
-Groups
-Administrators
September 16, 2011 at 2:10 pm
Both the SQL server and SQL agent are running under my local domain account which is part of Administrators group.
What I found is that Copy and restore jobs show the following in history.
Message
Executed as user: ServerInstanceName\SYSTEM. The step succeeded.
No idea why the jobs are not running under my local domain account.
Surprisingly Copy job is working fine but the restore job still is not restoring the logs.
September 16, 2011 at 5:20 pm
Lets rule out some options..
1. Did you restore the Full Backup on secondary with NO RECOVERY option and is the secondary database in Restoring mode ?
2. Are the backups getting corrupted over the network during the backup or copy ? (try to restore the first Log backup after the FULL manually with NO Recovery .. If the Restore Fails or when trying to restore the file if SSMS doesn't recognize the Log Backup file then they are getting corrupted )
Also instead of directly backing up to the secondary server (not a safe method), try to backup on to the local server shared folder or a network share and then let the Copy and Restore jobs run ..
Thank You,
Best Regards,
SQLBuddy
October 22, 2013 at 8:43 pm
[RESOLVED!] I had the exactly same issue. I had manually initialized the secondary database but not to a point in time after the primary database had been enabled as a primary. I.e. I used an old backup file. I had all the logs from the backup through to the current time in the folder but the agent job would not restore them.
I manually restored all the log files to a point in time after the primary was enabled as a primary and the agent jobs started to work.
I probably should have enable the primary and then taken a backup but I am shipping half way round the world on a slow link so was using what I had.
Hope this helps someone in the future.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply