SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 Log Shipping Restore job failing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ksr39
Posting Yak Master

India
179 Posts

Posted - 05/07/2012 :  13:09:19  Show Profile  Reply with Quote
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

USA
192 Posts

Posted - 05/08/2012 :  06:50:58  Show Profile  Visit prett's Homepage  Reply with Quote
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

India
179 Posts

Posted - 05/08/2012 :  10:18:02  Show Profile  Reply with Quote
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

USA
192 Posts

Posted - 05/08/2012 :  22:56:48  Show Profile  Visit prett's Homepage  Reply with Quote
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 - 11/28/2012 :  13:36:59  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000