NORECOVERY MODE / STANDBY MODE?

[Former Member]

Hi,

Background:
Source Database A 
Target Database B

1. Apply backup from System A to System B ( NORECOVERY MODE / STANDBY MODE)
2. Keep Database B in ( NORECOVERY MODE / STANDBY MODE)
3. Apply transactional logs everyday.
4. Continue applying Transactional logs for 15 days.
5. Recover database from ( NORECOVERY MODE / STANDBY MODE) after 15 days.

Question :- Which mode is better  NORECOVERY MODE or STANDBY MODE for faster step 5? ( faster recovery ). Which one is recommended?

Regards,

Arpit.

[sunny_pahuja2 · Accepted solution]

Hello,

With ‘No recovery mode’ you have no read access to the database and with ‘Standby mode’ you have read access to the database. Irrespective whether there is read access or not you cannot start SAP until the database is promoted to primary.

With ‘No recovery mode’, the promotion to primary might take few extra minutes, but does not allow read access to the database. Additional transaction log applications will be quick as there is no need to save and apply incomplete transactions for every transaction log restore.

  With ‘Standby mode’, the promotion to primary is quick, but the database offers read access. But additional transaction log applications might be slow (as there is a need to save and apply incomplete transactions for every transaction log restore, these incomplete transactions can be gigabytes in size and can make the transaction log much slower), in some cases this can even create a lag in application that the secondary standby database server is never able to catch-up.

Thanks,

Sunny

[Sriram2009]

Hi

1. Kindly refer the SAP Note 1420452  - FAQ: Restore and recovery with MS SQL Server Question No 4 and different between NORECOVERY and  STANDBY MODE, http://www.allinterview.com/showanswers/109103.html

2.

5. Recover database from ( NORECOVERY MODE / STANDBY MODE) after 15 days.
Question :- Which mode is better  NORECOVERY MODE or STANDBY MODE for faster step 5? ( faster recovery ). Which one is recommended?     

For what propose you are doing this system A to B restore? Better you can do either the Database mirroring or Log shipping  as mention below SAP Notes

DB Mirroring refer the SAP Note  965908  - SQL Server Database Mirroring and SAP Applications

DB Log shipping refer the SAP Note  493290  - Configuring SQL server Log shipping

Regards

Sriram

[Former Member]

Hi Sriram,

We are not using DB Mirroring  or log shipping because there is network connection betweem System A and System B.We have to copy logs through USB.

We are applying logs for 15 days and then we are going to recover database.

So after applying so many logs,recovery from STANDBY state or NORECOVERY State will be faster? Or there won`t be any significant difference?

Which one you will recommend?

Regards,

Arpit.

[Sriram2009]

Hi Arpit

As you said that you are going to apply the Log for 15days, in that case at the time of restoration, from the day 1 to 14 you can restore the DB in No recovery mode and 15 days log you can restore the recover mode

Regards

Sriram