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.
| 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 ModeBackup 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 :Message2012-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 SpecifiedMessage2012-05-05 00:30:01.09 Skipped log backup file. Secondary DB: 'X', File: '\\ABCD12DB07.emea.com\D$\logshippingbackup_x\X_20120504210001.trn'Message2012-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: 02012-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 appreciatedThank 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 |
 |
|
|
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. |
|
|
|
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... |
|
|
|
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! |
|
|
|
granuharmot
Starting Member
31 Posts |
Posted - 2014-09-23 : 05:11:05
|
| unspammed |
|
|
|
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 helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
|
|
|
|
Log Shipping Restore job failing