Log Shipping Restore Log on Secondary DB not restoring
-
I have a SQL 2000 database that was migrated to a SQL 2008 R2 instance but the compatibility mode still remains at 80. I am trying to set up log shipping to another instance in a cluster also a SQL 2008 R2 instance. All 3 agent jobs run successfully but the secondary database is never updated.
The first job backs up the primary, I see the trn files created. The second job copies the trn file to the local folder, I can see them too. The final job that restores the log file on the secondary database runs successfully, but doesn't update the database so data/users on the primary are not getting moved over to the secondary.
The SQL error logs show no errors. I looked into running a trace, but I don't see events specific to log shipping. At this point, I don't know how to troubleshoot this problem.
Thanks for any help,
Jeff Langdon
Update:
I have run the following queries but nothing stands out, except for when I query the restorehistory table I get nothing which is expected, I guess. 🙂
USE msdb;
GO
select * from log_shipping_secondary
select * from log_shipping_monitor_secondary
SELECT * from dbo.sysjobs WHERE category_id = 6
SELECT * FROM [msdb].[dbo].[sysjobhistory]
where [message] like '%Operating system error%'
order by [run_date] desc , [run_time]
SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
where [message] like '%Operating system error%'
SELECT * FROM [msdb].[dbo].[restorehistory]
Order by restore_date desc
-
Jeff, what happens if you run the command in the job manually? Have you traced through that command to see what it does?
-
crazyjoe (9/13/2012)
Jeff, what happens if you run the command in the job manually? Have you traced through that command to see what it does?Basically the same, it runs succesfully, but nothing gets updated. Not sure what you mean by "traced through that command". Could you explain?
-
Even though the job reports success, I would still look at the agent history and in the second step from the top. There should be some detail about why it wasnt able to perform any work. My guess off the top of my head is a permissions issue.
-
Another thought is maybe the restore job is looking in the wrong directory, and the job doesn't find any backups that need restoring, so it reports success.
-
check the restore job history and there should be some useful information.
-
Adam Haines (9/13/2012)
Even though the job reports success, I would still look at the agent history and in the second step from the top. There should be some detail about why it wasnt able to perform any work. My guess off the top of my head is a permissions issue.Looks like you are right about not finding the correct trn file. The final message in the Restore Agent History is "Skipped log backup file. Could not find a log backup file that could be applied to secondary database". It states that it restored 0 and that it ran successfully.
What are the reasons the Agent Job could find the correct trn? The first job, back up log, runs every hour at the top of the hour. The second job, copying the trn file to local folder, runs at 15 minutes past, and the final job, restoring the log backup runs at 30 past the hour.
-
Jeff Langdon (9/13/2012)
Adam Haines (9/13/2012)
Even though the job reports success, I would still look at the agent history and in the second step from the top. There should be some detail about why it wasnt able to perform any work. My guess off the top of my head is a permissions issue.Looks like you are right about not finding the correct trn file. The final message in the Restore Agent History is "Skipped log backup file. Could not find a log backup file that could be applied to secondary database". It states that it restored 0 and that it ran successfully.
What are the reasons the Agent Job could find the correct trn? The first job, back up log, runs every hour at the top of the hour. The second job, copying the trn file to local folder, runs at 15 minutes past, and the final job, restoring the log backup runs at 30 past the hour.
Path is incorrect is most common cause of that. Other is that permissions on directory are insufficient, although I think you might get a different error in that case.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Also, can you expand out the restore agent history and get the detailed error message?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (9/13/2012)
Also, can you expand out the restore agent history and get the detailed error message?Here is the history, finally message trunacted, but you get the idea.
-
Make sure the SQL Server accounts (DB Engine & Agent) running the secondary instance have permission to the directory where the backups are stored and permissions to the share itself. You can even try do a test restore manually with a backup in that directory, to see if the DB Engine has permission to that share/directory.
-
Hmm - " ... that could be applied to secondary".
I think you are missing one or more log files BEFORE the ones that are in the restore directory maybe? They are there, and you can see them (has actual file/path name in error message). Check to see if you have any gaps in the log file series, or if something was done on primary to interupt the LSN chain.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
After seeing the screenshot, I tend to agree with Kevin.. It is likely a LSN problem here.
-
take a differential backup on the primary and restore it to the secondary to restart LS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
You can run
restore headeronly against your backup files and look at the databasebackuplsn and then you can walkthrough the log backup lsn chain from here and make sure they match.
ex.
restore headeronly from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test.bak'
restore headeronly from disk =N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test_Log2.trn'
restore headeronly from disk =N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test_Log3.trn'
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply