Log-Shipping: Why would you choose No Recovery mode?

sql-server standby log-shipping
LondonPhantom picture LondonPhantom · Jan 21, 2013 · Viewed 18.8k times · Source

When configuring LogShipping for SQL Server, you can choose for the secondary database to be in No Recovery mode or Standby mode. No Recovery means you have no access to the database while log shipping is going on. Standby gives you read-only access, and if you select the option to disconnect users whenever a restore is about to happen, would appear not to interfere with the log shipping process. This looks to me like an extra benefit of standby mode, but as far as I can see the documention mentions no adverse affects.

I'm therefore wondering why anyone would choose to use No Recovery mode? The only plausible reasons I can think of are if Standby mode caused a significant performance degredation (but there's no mention of anything like that in the docs), or if there is some security requirement to actively prevent anyone seeing the contents of the secondary database (which would seem rare/unlikely).

Can anyone enlighten me what the advantage of choosing No Recovery mode is supposed to be?

Answer

Joachim Isaksson picture Joachim Isaksson · Jan 21, 2013

When you use NORECOVERY mode, no access will be given to the target database, so the database does not have to care about uncommitted transactions. The log can just be restored "as is" and left in that state.

When you use STANDBY mode, the database restores as NORECOVERY, then analyzes and rolls back all uncommitted transactions in the log. It can then give read only access to users. When the next log is restored, the database disconnects all users and rolls the uncommitted transactions from the last log forward again before restoring.

As you can see, STANDBY has potentially large extra overhead at restore, depending on your transaction volume.

More details at this article at My World of SQL.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More