Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 Log Shipping Restore job failing

Author  Topic 

ksr39
Posting Yak Master

193 Posts

Posted - 2012-05-07 : 13:09:19
Hi Experts,

please help me as i am not able to find the problem where i went wrong.

I am trying to configure Log Shipping between a production and DR Server in SQL server 2008 with out Monitor Server. The configuration of Log Shipping seems to be working successfully,


The jobs are all running as per my intended schedule. Logs are being created and shipped (appearing in the intended directory of the secondary server).

Before setting up Log Shipping I did a manual restore of a full backup and immd T-Log backup of the database from Prod to DR with 'standby' option, and then during LogShipping Config chose the option 'No, The Secondary Database is Initialized'

The database in DR is in Standby/ReadOnly Mode

Backup job from prod and Copy job from DR is running with out any issues and I am able to see copied Log files in Secondary/DR server folder.

The problem occurs when the LSRestore Job runs. It reads all the available Log files, but chooses to ignore them all and returns the following messages...

Skipped Log Backup File .....
Could not find a log backup file that could be applied to secondary Database.

The Restore job seems to be successfully completed, but nothing is restoring all the time.

Error Message :

Message
2012-05-05 00:30:00.92 Starting transaction log restore. Secondary ID: '322701ba-5e6f-49f7-a4a4-0566d1dcda07'
2012-05-05 00:30:00.92 Retrieving restore settings. Secondary ID: '322701ba-5e6f-49f7-a4a4-0566d1dcda07'
2012-05-05 00:30:00.92 Retrieved common restore settings. Primary Server: 'ABCDB11', Primary Database: 'X', Backup Destination Directory: \\ABCD12DB07.emea.com\D$\logshippingbackup_x', File Retention Period: 2880 minute(s)
2012-05-05 00:30:00.94 Retrieved database restore settings. Secondary Database: 'X', Restore Delay: 1, Restore All: True, Restore Mode: Standby, Disconnect Users: True, Last Restored File: <None>, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified

Message
2012-05-05 00:30:01.09 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\X_20120504210001.trn'

Message
2012-05-05 00:30:01.14 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\Xe_20120504211500.trn'
2012-05-05 00:30:01.17 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\X_20120504213001.trn'
2012-05-05 00:30:01.19 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\X_20120504214500.trn'
2012-05-05 00:30:01.22 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\X_20120504220000.trn'


2012-05-05 00:30:01.31 Could not find a log backup file that could be applied to secondary database 'X'.
2012-05-05 00:30:01.31 The restore operation was successful. Secondary Database: 'X', Number of log backup files restored: 0
2012-05-05 00:30:01.31 Deleting old log backup files. Primary Database: 'X'
2012-05-05 00:30:01.33 the restore operation was successful. Secondary ID: '322701ba-5e6f-49f7-a4a4-0566d1dcda07'
2012-05-05 00:30:01.33 ----- END OF TRANSACTION LOG RESTORE -----

Exit Status: 0 (Success)

I tried restore the copied logs manually and it is working.But don’t know why as per the schedule, restore job is not able to restore the logs.

Note : One more database is configured as logShipping in the same Prod and DR.That is working perfectly with out any issues.

Also, SQL server and Agent is running on Domain\Service Account.So, there is no point of sufficient permissions issue.

Any help greatly appreciated

Thank you all in advance....

prett
Posting Yak Master

212 Posts

Posted - 2012-05-08 : 06:50:58
First check that the Sql service account in secondary server has sufficient permissions to the shared folder in which the log backup is being copied to perform restoration.

Also check this post which have some causes & solutions about this error: http://www.sqlservercentral.com/Forums/Topic438664-146-1.aspx
Go to Top of Page

ksr39
Posting Yak Master

193 Posts

Posted - 2012-05-08 : 10:18:02
Hi Prett thank you for the reply,

yeah Sql service account is having enough permissions to the shared folder.
Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2012-05-08 : 22:56:48
quote:
Originally posted by ksr39

Hi Prett thank you for the reply,

yeah Sql service account is having enough permissions to the shared folder.



Did you check the forum post??
It has some other solutions...
Go to Top of Page

dsithoo
Starting Member

2 Posts

Posted - 2012-11-28 : 13:36:59
I know this is an old forum, but I had the exact same situation happen to me as well, and I found that no t-logs were restored until there was atleast 1 active transaction in the database being log-shipped. I had to fake a transaction on the primary database before log-shipping miraculously started to restore the logs from the point the first active transaction was backed-up. I am using SQL 2005, but I suspect you have the same issue, especially given that you are on a test system also which probably didn't have any active transactions to begin with!
Go to Top of Page

granuharmot
Starting Member

31 Posts

Posted - 2014-09-23 : 05:11:05
unspammed
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-07 : 06:29:56
quote:
Originally posted by prett

First check that the Sql service account in secondary server has sufficient permissions to the shared folder in which the log backup is being copied to perform restoration.

Also check this post which have some causes & solutions about this error: http://www.sqlservercentral.com/Forums/Topic438664-146-1.aspx



To be more clear its not SQL server service account but SQL server agent account which needs to have privilege on folder from where it is picking trn log files for restoring. The account must have read and write privileges.


Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -