Disater recovery test by using logshipping

Question

Disater recovery test by using logshipping

Mani-584606

SSChampion

Points: 11788
More actions
November 30, 2009 at 1:37 am

#137166

Hi,
I'm testing Disater recovery by using logshipping in SQL Server 2005. Log shipping has already configured and it's working fine. For the DR test, I'm performing the following:
1. Make sure the last trasaction log backup of primary has copied to secondary and applied to secondary database.
2. Take the Tail backup of Primary database and keep the Primary database in restore mode.
3. Copy the Tail backup of Primary database to secondary and restore it with recovery and bring the secondary database online.
4. Make the application point to secondary and users do their work and sign off.
5. Take the Tail backup of secondary database and keep the secodary database in restore mode.
6. Copy the Tail backup of secondary database and restore it on primary with recovery and bring the primary database online.
7. Make the application point to primary and make sure the log shipping is working again from Primary to Secondary as usual.
I have tested the above steps and it's working fine. The Log shipping also working from Primary to Secondary as it used to work prior to DR test. Does this Plan makes sense? I want to have your opinion, before performing the above steps in Production.
many thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 1

One thing I would suggest is that while testing in DR, ensure that you script out everything and use the scripts in production by few changes.

Ensure that you take backups of production db for disaster.

Also once secondary is back to production server please allow & test min connections.

josantia Ten Centuries Points: 1255 More actions · Answer 2

Hi,

How are you going to handle the drop-create users in the backup server? Do you have your scripts ready for this?

I would suggest also disabling all jobs in primary server and enabling it again when you decide to return the operation to this server. You can script this out.

Regards.

Jose Santiago Oyervides

Mani-584606 SSChampion Points: 11788 More actions · Answer 3

Thank you,

Hi,
How are you going to handle the drop-create users in the backup server? Do you have your scripts ready for this?

we are not going to create/drop any users. We just create some transaction after bringing the secondary online & then by take the tail backup of secondary and apply to primary and bring it online & verify the transactions created on secondary.

thanks

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

How will you set the primary to restoring? you can't back up the tail log, set the database to restoring, and then restore a log from the secondary. You will need a full backup from the secondary to failback.

Mani-584606 SSChampion Points: 11788 More actions · Answer 5

How will you set the primary to restoring? you can't back up the tail log, set the database to restoring, and then restore a log from the secondary. You will need a full backup from the secondary to failback.

But I'm able to take the tail log, set the primary to restoring and then restore a log from the secondary and apply to primary and bring online. I have tested this and it's working for me. Please, Please advice me because we have scheduled to follow this method for production database to test Disaster recovery.

If this is NOT going work for any reason please advice me. So that we go alternative approach.

1.

I'm setting the Primary in restore mode by taking the Tail backup & leaving the primary in restore mode. I'm using below script:

BACKUP LOG [Mydb_Primary] TO DISK = N'Z:\Taillog_Backup\Mydb_Primary_Tail.trn'
WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'Mydb_Primary-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Mydb_Primary'
and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Mydb_Primary' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Mydb_Primary'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'Z:\Taillog_Backup\Mydb_Primary_Tail.trn'
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

2.

Then, I will apply the above Tail log backup 'Mydb_Primary_Tail.trn' to the secondary (which is already in restore mode) and bring it online. I'm using below script:

restore log Mydb_Primary
from disk='Z:\Pri_Taillog_Backup\Mydb_Primary_tail.trn'
with recovery

3. Point the applications to connect Secondary and do some transactions.

4. Agin take the tail log backup and leave the secondary in restore mode

5. Apply the above Tail log backup (from step 4) to Primary(which is in restore mode) and bring it online.

6. Verify the transactions entered at secondary, after bringing the Primary online

7. Verify the Log shipping working as usual from Primary to Secondary.

the above steps working for me..If there is any issue in performing the above steps please correct me

thank you

Mani-584606 SSChampion Points: 11788 More actions · Answer 6

Hi Steve Jones,

I'm waiting for your advice before performing the above steps(which worked in my Test environment) in Production. As you said we cannot do this, I want to know the reason behind this and how it's working for me in the Test environment.

Please advice

thank you so much

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

This should work. I was thinking that I couldn't put a database back in standby, but you can with the BACKUP command.

If you ensure that you are not getting transactions on the primary after the backup, you should be OK.

Mani-584606 SSChampion Points: 11788 More actions · Answer 8

Thank you very much for the clarification steve

Thanks

Mani-584606 SSChampion Points: 11788 More actions · Answer 9

Hi,

Is there a t-sql command to know the last performed Tail backup on Primary? and last restored tail backup on Secondary?

And on Primary, the last transaction log backup happened is at 7:50 AM(via log shipping backup job ) lets say xyz.trn and then Log shipping backups job has disabled at 8:00 AM.

Now the transaction are happened between 8:00 AM 12:00 PM (assume that lot of transaction log (in GB) generated) then if I take the tail backup ,lets say the tail log file abc.trn, by putting the database in restore mode at 12:05 PM, will all the transactions performed between 8 to 12 (3 hrs) backed up in that tail backup i.e abc.trn?

From the link http://msdn.microsoft.com/en-us/library/ms179314(SQL.90).aspx I have read that

a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes

So how can we know whether the database contain any bulk-logged changes?

please advice

Mani-584606 SSChampion Points: 11788 More actions · Answer 10

Hi,

Is there a t-sql command to know the last performed Tail backup on Primary? and last restored tail backup on Secondary?

I found the below script to find last performed Tail backup on Primary:

select name,backup_finish_date,has_incomplete_metadata from msdb.dbo.backupset

order by backup_finish_date desc

Resluts:

name backup_finish_date has_incomplete_metadata

Mydb-TransactionLog Backup2009-12-02 12:05:00.0000 - this is the time I ran the tailbackup

NULL 2009-12-02 7:50:00.0000 -this is the time last t-log backup ran via log shipping backup. But why it's showing the name as NULL?

and Please clarify me the below:

On Primary, the last transaction log backup happened is at 7:50 AM(via log shipping backup job ) lets say xyz.trn and then Log shipping backup job has disabled at 8:00 AM.

Now the transaction are happened between 8:00 AM 12:00 PM (assume that lot of transaction log (in GB) generated) then if I take the tail backup ,lets say the tail log file abc.trn, by putting the database in restore mode at 12:05 PM, will all the transactions performed between 8 to 12 (3 hrs) backed up in that tail backup i.e abc.trn? Does tail backup will work, if many transactions occur? Does tail backup makes sense in this case?

From the link http://msdn.microsoft.com/en-us/library/ms179314(SQL.90).aspx I have read that

a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes

So how can we know whether the database contain any bulk-logged changes?

please advice

Mani-584606 SSChampion Points: 11788 More actions · Answer 11

Mani-584606

SSChampion

Points: 11788

December 3, 2009 at 3:59 pm

#1087423

Could you please advice me.

thanks

gary1 SSC-Insane Points: 21152 More actions · Answer 12

go through this link http://www.windows-tech.info/15/1d7cfc521f352be2.php

from the above link, your scenario should work and Tail backup can be of any size (may be in GB) depending on the transactions occurred and it will include all the transactions occurred after the last transaction log backup. So all your transactions (8:00 AM to 12:00 PM) should be there in your Tail backup of Primary taken at 12:05 PM

I hope forum members will correct if my answer is wrong...

thanks

Mani-584606 SSChampion Points: 11788 More actions · Answer 13

Thanks,

We performed the exact steps, discussed in this thread, in Production and everything worked well.

thanks for your help