January 3, 2008 at 3:28 pm
I am trying to configure Log Shipping between a production and tests Server. The configuration of Log Shipping seemed to work successfully,
3 SQL Server Agent jobs have been created on the Secondary Server.
LSAlert
LSCopy
LSRestore
1 SQL Server Agent job has been created on the Primary Server
LSBackup
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 of the database from Prod to Test with 'standby' option, and then during LogShipping Config chose the option 'No Secondary Database is Initialised'
The database in Test is restored and in Standby/ReadOnly Mode
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 job finishes OK, but nothing is restored. After a few hours of this the Monitor job starts to complain that the Secondary Database is out of Sync, but I believe this to be a symptom rather than the cause.
Any help greatly appreciated
Neil
January 5, 2008 at 11:24 pm
respected sir,
i want to transfer the database after a fixed interval time on to the another server how can i do it.
and that transfer database is automatically restore on to the another server.
and from the another server i want to see the reports
please help me.
January 6, 2008 at 8:59 am
Neil,
Pls check if the Sql service account in secondary server has sufficient permissions to the shared folder in which the log backup is being copied to inorder to perform restoration...check the job history in secondary server to find the exact error message and post it here..the out of sync error appears if the log backup is not restored in the secondary for a specified amount of time..
[font="Verdana"]- Deepak[/font]
January 6, 2008 at 9:02 am
Pravin, your scenario can be accomplished by log shipping in SQL 2000 and SQL 2005. You can refer these links for more info,
http://sql-articles.com/articles/lship/lship.htm
[font="Verdana"]- Deepak[/font]
January 6, 2008 at 10:20 pm
respected sir,
i need all steps of the logshipping i want to transfer the data one server to another server in sql server 2005 and transfered data wants to automatically restore on another server and what are jobs i have to created and how it's created and from another server i want to see the Reports. please if possible give me all detailed steps.i am very thankful to you for giving me response.
January 6, 2008 at 11:00 pm
Pravin,
All the required information is available in the link i gave above...you have the following topics listed in the link I provided earlier.,
Topics
*****
What is Log Shipping?
Pre-requisites for Log Shipping
10 Steps to configure Log Shipping
Fail-Over in Log Shipping
Role Change in Log Shipping
[font="Verdana"]- Deepak[/font]
January 7, 2008 at 2:55 am
Hi it may be that the account used by you sql server agent is a local account rather than a domain account and/or does not have enough privilages to connect to or update the monitor server
But you the following check list might help a little
Make sure that the SQL Server Agent service is running for the secondary server instance and that the restore job for this secondary database is enabled and is scheduled to run at the appropriate frequency.
The restore job on the secondary server might be failing. In this case, check the job history for the restore job to look for the cause.
The log shipping restore job, which runs on the secondary server instance, might not be able to connect to the monitor server instance to update the log_shipping_monitor_secondary table. This might be caused by an authentication problem between the monitor server instance and the secondary server instance.
The backup alert threshold might have an incorrect value. Ideally, this value is set to at least three times the frequency of the restore job. If you change the frequency of the restore job after log shipping is configured and functional, you must update the value of the backup alert threshold accordingly.
When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_secondary table is not updated with the current values before the alert message job runs. Error 14421 can be raised when a restore job succeeds with, "Could not find a log backup file that could be applied to secondary database." When this occurs, the restore time is not updated. The cause of the error in this case might be an issue with the copy job.
To update the monitor tables with the latest data for the secondary database, run sp_refresh_log_shipping_monitor on the secondary server instance.
On the secondary or monitor server instance, the date or time is incorrect. This may also generate alert messages. Possibly the system date or time was modified on the one of them.
CodeMinkey
January 7, 2008 at 8:32 am
Try restoring the logs manually to the standby server and see what, if any, error is received.
In the past while debugging, I have found alerts set up on production systems to monitor transaction log growth. When the log is 80% full, the alert truncates the log. Of course, this was done by an an inexperienced DBA attempting to stop "transaction log full" error messages.
Obviously, this would affect log shipping.
January 7, 2008 at 8:13 pm
Janet, Keith, Deepak,
Thank-you for all your shared experience and advice. I tried a different tack, and worked. My Primary Database had alkready been configured for Transaction Log Backups. The backups were produced with naming convention dbname_backup_timestamp.trn. When I configured log shipping, a 2nd set of transaction log files was produced with namimg convention dbname_timestamp.trn. These 2nd files were then shipped successfully, but I believe the log sequencing with the files was disrupted by the initial transaction log backups.
So...
- I stopped transaction log backups on the primary database
- De-configured log shipping (is that a word ???? 🙂 )
- took a new FULL backup of the primary database
- restored this to the 2ndary database (with standby option)
- deleted ALL .trn file on primary and 2ndary servers
- Reconfigured log shipping
the restore job now picks up logs and reports that it has restored them.
Regards, Neil
ps. I think I have another problem with availability of the 2ndary database now. It shows up in SQL Management Studio as (restoring...) and the database cannot be opened. I hope this resolves itself as the restore 'catches up'.
- configured log shipping
January 7, 2008 at 10:49 pm
Hi Neil,
This will most likely be due to the recovery option you have selected when you reconfigured log shipping. You need your log shipping setup in Standby mode which will leave the secondary database online, but read only.
So maybe you want to re-initialise log shipping again, but this time choose the Standby mode.
There is a better explanation here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
Hope That Helps - Nathan
January 7, 2008 at 10:55 pm
Aaah Nathan, thankyou. If only I could find these replies before spending the day working it out for myself (OK, with Google then..) Yes, you are right, I will have to re-initialise the log-shipping process again, do another backup and restore, and re-start. This time I think it WILL work as I desire.
December 17, 2009 at 6:14 am
Hi,
I have this problem also. All the jobs execute successfully, but the restore job checks each transaction log and skips all of them. I can manually load the first valid one, and the next ... and then after the next backup, copy restore cycle... the restore job just carries on skipping all the logs.
Anyone know - how is it storing the LSN it's looking for?
Cheers,
R
January 26, 2010 at 12:41 am
I was noticing the same thing, and what I discovered was that Primary was backing up and shipping to secondary just fine, and restores were being attempted. However, since there were zero changes, everything was being skipped. I forced a change on Primary, and it found the correct file and restored. No error, and alerts went away.
The bummer of all this is that it's not smart enough to log the attempt. So if you have a small database with zero changes say for 2 hours, and you set your alert for 45 minutes, it's going to scream at you often even though there is no issue.
February 10, 2012 at 9:53 am
I have same issue.
Where did you forced the restore option or which changes you made on primary server??
Thanks in advance.. 🙂
February 10, 2012 at 10:03 am
Jinu43 (2/10/2012)
I have same issue.Where did you forced the restore option or which changes you made on primary server??
Thanks in advance.. 🙂
You can do the 1st restore log after full backup in SSMS manually
and see if it fixes now.
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply