cancel
Showing results for 
Search instead for 
Did you mean: 

MS SQL : Transactional log application

Former Member
0 Kudos

Hi Experts,

I am planning to install new system by homogeneous system copy. (Windows 2008 / MS SQL 2008 ) as below.

  1. Take a full backup and log backup from system A.
  2. Install Central Instance and database software on System B.
  3. Create and attach database by database specific procedure [ Backup restore ] (Note 151603 )
  4. Perform follow-up tasks on system B.

Now I want to apply transaction logs from system A to newly installed system B every day.

Q: If I follow below procedure, will that be OK?

  1. ftp transaction log files from system A to System B.
  2. Recover database using below commands.

RESTORE LOG <database name of system B> FROM <backup_device> WITH NORECOVERY;

RESTORE DATABASE <database_name> WITH RECOVERY;

  As system B is currently running, Will it be possible to apply transaction logs to system B by above method. I think some think is missing.

Database on System B is already running. Will it be possible to apply logs by above mothod? Please correct my steps.

Regards,

Arpit.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Tony,

We can not use MS SQL log Shipping functionality as there is no connection between system A and system B. We have to manually collect logs and move to system B by media. ftp might not be possible I Guess.

Now, when I recover database on system B by full backup and transaction log backup.

Can I apply Transaction log backup next day to the same database?

I am confused as I am trying to compare the process with Oracle where we used to apply offline backup to DR and then archive logs . But if we opened DR database, then again we have to apply full backup.

I am not sure if I am framing this query properly.

Regards,

Arpit.

Private_Member_12188
Active Participant
0 Kudos

You are going to want to leave the SAP system B off and the database B on.  If you want to be able to restore daily log backups, then Database B needs to be left in a restoring state.  You will not be able to directly access this database while it's in a restoring state.

Former Member
0 Kudos

Hi Tony,

I think you understood my question. Thank you.

As I mentioned , we are performing homogenous system copy. ( from system A to B ).

So database B will be left in online state.

You said, database B needs to be left in a restoring state for daily log backup

I seached online material and found below

A best practice is to restore all the log backups (RESTORE LOG database_name WITH NORECOVERY). Then, after restoring the last log backup, recover the database in a separate operation (RESTORE DATABASE database_name WITH RECOVERY).

But for us transaction log backup will be not available at one time. We need to collect daily and apply it for 15 days.

Then after 15 days , we can recover database and make it online. This approach should work after 15 days else we will waste 15 days.

I think I should follow below approach.

  1. We should do system copy and apply transactional logs available. So database B will online.

  2. Check if everything is ok with System B.

  3. If system B is OK, scrap the system B ( Sap system B and database B )

  4. Start system copy again.

  5. Apply full backup , restore logs with NORECOVERY.

  6. continue applying logs for 15 days.

  7. Then recover database after 15 day and complete system copy.

Any thoughts?

Regards,

Arpit.

Former Member
0 Kudos

Hi ,

I am closing this. I will raise another question to proivde more clarity.

Regards,

Arpit.

Answers (2)

Answers (2)

Private_Member_12188
Active Participant
0 Kudos

You are going to want to shut off SAP on system B, then following the below process for transaction log shipping.

http://msdn.microsoft.com/en-us/library/ms190640(v=sql.100).aspx

Microsoft SQL Server has a built in process to do what you want.

former_member188883
Active Contributor
0 Kudos

Hi Arpit,

As per my understanding Database B has to be running for logs to be applied. SAP application should be down in this case until recovery is complete.

Regards,

Deepak Kori