How to determine how many transaction log need to restore in log shipping?

Question

How to determine how many transaction log need to restore in log shipping?

Rajat Jaiswal-337252

SSCommitted

Points: 1779
More actions
August 10, 2010 at 4:16 am

#225909

Hi,
I have implemented log shipping. its working fine for me but
i have to try fail case also.
so i have just stoped log shipping. but i am not aware of how many transaction restored yet.
and i explicity restored the secondary database with No Recovery option.
and its restoed but i could not get last records.
Because some transaction back not restored at that time.
I tried to restore the transaction backup but unable to do that.
any help ??????
Thanks in Advance.
Thanks
Rajat

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

simplest way is by the timestamp on the backup files. Remove any log backups from the log backup directories on primary and secondary which are previous to the full backup you used to reinitialise secondary. Log shipping will automatically pick up from next available log backup left in the log backup directory.

---------------------------------------------------------------------

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 2

I have also implemented Log shipping according to your document on 110 GB database. Now i am worried when primary server fail we will restore Secondary server with “no recovery option” and suppose after 2 days primary server is up till that time my secondary server act as primary server.

But when my primary server is ok i need it back in action.

My Question is do we need to follow same action again to establish log shipping or is there any other way.

Thanks

Rajat

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Rajat Jaiswal-337252 (8/11/2010)
I have also implemented Log shipping according to your document on 110 GB database. Now i am worried when primary server fail we will restore Secondary server with “no recovery option”

when making the secondary live you ensure all logs are restored and then run 'restore database dbname with recovery - not norecovery so not sure why you say that

and suppose after 2 days primary server is up till that time my secondary server act as primary server.
But when my primary server is ok i need it back in action.
My Question is do we need to follow same action again to establish log shipping or is there any other way.
Thanks
Rajat

If your primary server failed and the secondary has been the live database then the old primary is now out of date. In almost all cases this means you have to re-initialise the old primary by doing a full restore from the secondary. Presuming the log shipping jobs still exist you can then clear out the log backup directories (save contents away temporarily) and just restart the log shipping jobs.

the exception is if you were able to backup the tail of the log (backup log with norecovery) when the primary failed and you have all the log backups from the secondary whilst it was live, then you may be able to bring the primary up to date from the log backups.

Set up a small test database and do come testing till you are comfortable with this.

---------------------------------------------------------------------

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 4

Hi,

Sorry that "no recovery " was typo mistake.

Thanks for your response.

it means i have to take again 110 GB's Database from secondary database and restore on primary database for further log shipping process 🙁 its quite time consuming.

Thanks

Rajat

george sibbald SSC Guru Points: 104210 More actions · Answer 5

afraid so, but in a situation where primary is lost unavoidable.

In a test failover can be avoided by doing tail backup of the log.

---------------------------------------------------------------------

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 6

Hi

Thanks for reply.

can you please help with any solid way by which i don't need to do whole backup and everything go smoothly.

I am just guessing if its possible " take database backup after a particular date suppose primary server fail on 1 august and back in action on 4 august" so we just take backup from 1 to 4 august and restored it on primary server.

then remove secondary server and restore it with no recovery mode.

and implement log shipping again.

Is that feasible ?

or waste of time on large database ?

and how to take backup from 1 to 4 august ?

Thanks

Rajat

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Rajat Jaiswal-337252 (8/12/2010)
I am just guessing if its possible " take database backup after a particular date suppose primary server fail on 1 august and back in action on 4 august" so we just take backup from 1 to 4 august and restored it on primary server.
then remove secondary server and restore it with no recovery mode.
and implement log shipping again.

thats basically how you would re-establish it when the primary is back up after being down for a while.

take a full backup on the secondary

copy it to the primary and restore it there with recovery

restore the same backup to secondary with norecovery.

restart log shipping

---------------------------------------------------------------------