« Q) How do I add a root node and return columns as elements with FOR XML? | Main

Aug 12, 2010

Warm standby on SQL Server 2008 with log shipping from SQL Server 2005?

Question

Can you create a warm standby database on SQL Server 2008 with log shipping from a SQL Server 2005 database?

Answer

Each version of SQL Server has a database version number; for SQL 2000 it is 8, SQL 2005: 9 and SQL 2008: 10. When a database backup that came from a lower SQL Server version is restored, SQL Server will run an upgrade process to bring that older database version up to the database version supported by the new instance. This process is run automatically as part of SQL Server’s recovery process.  The recovery process itself is certain operations that need to happen to ensure the database is in a consistent state for user access (roll-forwards and roll-backs etc).


Placing a database in “Standby/Read Only” mode instructs SQL Server to recover the database after each restore, but also create a special standby file that contains information which will allow this recovery to be undone when it is time to do additional transaction logs restores. 

However unfortunately, once a database has been upgraded it cannot be undone.  This is why if you try and use the “Standby/Read Only” option when restoring a database of an older version SQL Server fails with:

 Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This error message essentially says you cannot bring an older database version online in “Standby/Read Only” mode.

But you can restore transaction logs and log shipping from an older database version to a newer database version when you keep the database offline.  This is because the recovery (and upgrade process) for the database is deferred until the point you do actually bring the database online.  This means you can log ship from an older version of SQL Server to a newer version until the point you bring that database online for user access.

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.

Posted by on Aug 12, 2010 at 00:33 in SQL Server Database Services |

| | | | |

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a013480b3f254970c01348627a9e6970c

Listed below are links to weblogs that reference Warm standby on SQL Server 2008 with log shipping from SQL Server 2005?:

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

Posted by:  | 

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment