March 15, 2004 at 12:59 pm
Hi everyone,
Could someone help me in restoring the transaction logs files. I am having a problem in restoring my transaction log files after the recovering of full backup on my standby server. I am trying to do a log shipping and for this purpose I have created a standby server linked with the production server. After testing the log shipping on my test servers I decided to implemented on the production server.
What is the manual way of restoring the transaction log files using the Query Analyzer and keep my standby server synchronized with the production server.
Thanx.
Muneeb.
March 15, 2004 at 1:26 pm
I am a newbie, but from what I read it seems the following should be true.
A full restore is performed on the standby server using the "norecovery" option. This allows for subsequent logs to be restored.
All logs created, in production, since the full restore was created (on production server) can then be applied using the "standby =" clause (after shipping to standby server).
Additionally, post full manual restore, you should set your databases to "read-only".
The logs on the standby server should NOT be applied using "recovery" by default or directly (unless you want to make the standby server an active server).
GaryA
March 15, 2004 at 1:39 pm
Hi Gary,
Thanx for the reply. Yes, I did what you have said. I am using the norecovery option and the standby option on the standby server with readonly.
Actually what happend was I restored the Thursday's night full backup on the standby server and after that I was keep restoring the transaction logs every 10 mins which worked fine and no error encountered. on Friday the transaction work fine as well on Thursday night backup with and the transaction logs when I restored earlier. But on Friday after the production servers full backup and I tried to restore the transaction logs for saturday (we don't have production on saturday and sunday) so I guess nothing is going to update. Well anyway, on Saturday I start getting errors every 10mins.
Thanx.
Muneeb.
March 15, 2004 at 3:43 pm
Hi Muneeb,
Please let me verify.
Friday a production server was backed up and then restored to your standby server. The production server recovery model was "full".
All logs created after the Friday full backup were then subsequently applied to the standby server, in the sequence they were created, using the same undo file each time. As you probably know the undo file needs the ability to grow over time.
Was there a Saturday backup of the production server?
Have you tried running DBCC Checkdb (refer to BOL) on the standby server?
GaryA
March 15, 2004 at 5:15 pm
Hi Gary,
Thanx for the reply.
Yes, a production server was backedup on Friday and restore on Saturday on the standby Server and the recovery model was full. The transaction logs were restored using the same undo file as well in sequence.
Yes, I am aware about the undo file.
and yes, on Saturday the server backuped up again but after this the problem had started.
No, I haven't used the DBCC utility.
Thanx.
Muneeb.
March 15, 2004 at 5:19 pm
Hi Gary,
Let me briefly explain how my Production and Standyby server is setup in the following paragraph.Production Server is Linked with Standby Server thru Linked Server. 2 job Agents are running at production Server one for the full backup and the other for the transaction log backup. The Transaction log backup performs every 10 min from 2:00 AM in the morning till 10:00PM at night (NO_TRUNCATE). The full backup starts at 10:30 PM every night executing following steps: * Perform Full Database Backup * Append Transaction Log backup (with NO_TRUNCATE) * Execute stored procedure on my Standby Server to restore the latest transaction log. * Zip Archieve Transaction log file in separate folder. * Backup Transaction Log (with TRUNCATE).After the full backup the normal transaction append continues thru my Job Agent from 2:00AMThat's how I prepared my Standby Server.On Friday morning, I restored the Full backup which was taken on Thursday night with all the Transaction logs. After that my job agent which runs every 10 mins for my transaction log backup restores transaction log on my standby server till 10:00 PM. then the Full Backup started @10:30PM for the Friday night database (Steps mentioned above). So far so good. Now here is the problem started. When I checked my Transaction log agent which starts @2:00AM on Saturday morning it fails every after 10mins...and send me 187 emails uptill now and I have to stop calling the standby server in both the job agents not to send the further emails.Do I have to restore every night full backup on my standby server then apply the transaction logs to keep it synchronize with the production Server? If I have to do this step then I think this beat up the purpose of my log shipping...Thanx..Muneeb.
March 16, 2004 at 1:06 am
http://www.sql-server-performance.com/sql_server_log_shipping.asp is a really good covering of this subject. I have based all my log shipping work on it with great success. I hope it helps. Regards from Paul
March 16, 2004 at 7:37 am
What error are you getting exactly? Check the job history and click the View Details checkbox.
March 16, 2004 at 10:31 am
Thanx Paul.
I appreciate your help. Actually my log shipping is also based on this article as well. It's really interesting article after applying this article on my test servers I applied it on the production server. but as I mentioned earlier my problem starts in the production phase. Please ready my above replies and give me your valuable suggestion.
Thanx
Muneeb.
March 16, 2004 at 11:06 am
Hi, first of all, have you managed to get the initial full database backup over to the standby server and have you restored it with something like this:
restore database MYDATABASENAME from disk 'yourbackupfile.bak'
WITH DBO_ONLY, REPLACE, STANDBY ='myundofile.ldf'
followed by the MOVE statements for where you want the database files?
March 16, 2004 at 11:12 am
First many thanks to Paul for providing access to an excellent article on Log Shipping.
Muneeb,
First let me state the obvious. As I read the article Paul references, the thought occurred to me that while some tasks may be implemented more than one way, it is still critical that all the tasks get performed.
As I read your response as to how your log shipping is designed, forgive me,
but somethings were not clear to me.
I have interspersed my questions into your problem descriptions, restated by me below.
------------------------------------------------
Your previous description is:
1. Production Server is Linked with Standby Server thru Linked Server.
2. Job Agents are running at production Server one for the full backup and
the other for the transaction log backup.
The Transaction log backup performs every 10 min from 2:00 AM in the morning
till 10:00PM at night (NO_TRUNCATE).
The full backup starts at 10:30 PM every night executing following steps:
* Perform Full Database Backup
? Is this backup restored to the standby server?
* Append Transaction Log backup (with NO_TRUNCATE)
? NO_Truncate option may be an error.
BOL says: Although the transaction log may be truncated manually,
it is strongly recommended that you do not do this, as it breaks the log backup chain.
* Execute stored procedure on my Standby Server to restore the latest transaction log.
* Zip Archieve Transaction log file in separate folder.
* Backup Transaction Log (with TRUNCATE).
After the full backup the normal transaction append continues thru my Job Agent from 2:00AM
That's how I prepared my Standby Server. On Friday morning, I restored the Full backup which was
taken on Thursday night with all the Transaction logs.
After that my job agent which runs every 10 mins for my transaction log backup restores
transaction log on my standby server till 10:00 PM. then the Full Backup started @10:30PM for the
Friday night database (Steps mentioned above).
So far so good. Now here is the problem started. When I checked my Transaction log agent which
starts @2:00AM on Saturday morning it fails every after 10mins...and send me 187 emails uptill
now and I have to stop calling the standby server in both the job agents not to send the further
emails.
Do I have to restore every night full backup on my standby server then apply the transaction logs
to keep it synchronize with the production Server? If I have to do this step then I think this
beat up the purpose of my log shipping...Thanx..Muneeb.
----------------------------------------------
HTHs
GaryA
March 16, 2004 at 11:38 am
First many thanks to Paul for providing access to an excellent article on Log Shipping.
Muneeb,
First let me state the obvious. As I read the article Paul references, the thought occurred to me that while some tasks may be implemented more than one way, it is still critical that all the tasks get performed.
As I read your response as to how your log shipping is designed, forgive me,
but somethings were not clear to me.
I have interspersed my questions into your problem descriptions, restated by me below.
------------------------------------------------
Your previous description is:
1. Production Server is Linked with Standby Server thru Linked Server.
2. Job Agents are running at production Server one for the full backup and
the other for the transaction log backup.
The Transaction log backup performs every 10 min from 2:00 AM in the morning
till 10:00PM at night (NO_TRUNCATE).
The full backup starts at 10:30 PM every night executing following steps:
* Perform Full Database Backup
? Is this backup restored to the standby server?
* Append Transaction Log backup (with NO_TRUNCATE)
? NO_Truncate option may be an error.
BOL says: Although the transaction log may be truncated manually,
it is strongly recommended that you do not do this, as it breaks the log backup chain.
* Execute stored procedure on my Standby Server to restore the latest transaction log.
* Zip Archieve Transaction log file in separate folder.
* Backup Transaction Log (with TRUNCATE).
After the full backup the normal transaction append continues thru my Job Agent from 2:00AM
That's how I prepared my Standby Server. On Friday morning, I restored the Full backup which was
taken on Thursday night with all the Transaction logs.
After that my job agent which runs every 10 mins for my transaction log backup restores
transaction log on my standby server till 10:00 PM. then the Full Backup started @10:30PM for the
Friday night database (Steps mentioned above).
So far so good. Now here is the problem started. When I checked my Transaction log agent which
starts @2:00AM on Saturday morning it fails every after 10mins...and send me 187 emails uptill
now and I have to stop calling the standby server in both the job agents not to send the further
emails.
Do I have to restore every night full backup on my standby server then apply the transaction logs
to keep it synchronize with the production Server? If I have to do this step then I think this
beat up the purpose of my log shipping...Thanx..Muneeb.
----------------------------------------------
HTHs
GaryA
March 16, 2004 at 2:35 pm
The full backup starts at 10:30 PM every night executing following steps:
* Perform Full Database Backup
? Is this backup restored to the standby server?
- Yes, but not always. my idea is to restore the full backup once and then apply the logs right after every 10 mins through my Transaction log agent which is running on the Production Server. Do I have to restore the full backup everytime?
* Append Transaction Log backup (with NO_TRUNCATE)
? NO_Truncate option may be an error.
- This step is usually perform to keep the whole day log backup before archieving.
Right now what I did is restore last night full backup then applied the transaction log archieved (NO_TRUNCATE) and later I applied the logs creating every 10 mins on my server and this works fine. But, I know I'll ran into a problem once the full backup agent will start performing the following steps:
1) Perform Full Backup (10:30)
2) Append Logs (with NOINIT)
3) Call my stored procedure on standby server to restore log in step 2
4) Zip Archieve the log file in step 2
5) Refresh and Truncate log file for the next day (with INIT, TRUNCATE_ONLY)
After performing step 5 the problem begins......
I don't restore the full backup on my standby server everyday or after step 5
Any thoughts....
Thanx to Paul for an excellent article..I used the article earlier to create my test servers when I started the log shipping thingee.
Thanx.
Muneeb.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply