Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Tips and Tricks : Database restore will fail in STANDBY mode

Posted by blakhani on August 5, 2014


One of the various methods to move user database from lower version to higher version is backup restore. I have seen various upgrade scenarios using log-shipping and database mirroring as well. In past few days, I have seen similar question from at least three friend and I was surprised. Below are the questions:

  • We are planning a move, our production database from server A (which is SQL2005) to our new server B (which is SQL2008 R2). We are thinking and testing setting up logshipping between these servers in standby mode. This is because few applications need only to read the data.
  • If my primary server is 2008 (standard enterprise edition) and 2008 r2 (standard edition), then wil it possible to restore 2008 databse back-up files on 2008r2 database using standby mode recovery model

When we try to restore in standby mode to higher version, we would get below error message

Msg 3180, Level 16, State 1, Line 2
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 2
RESTORE DATABASE is terminating abnormally.

This is applicable to any lower to higher version restore. The standby mode restore is not allowed because the system level information (table, columns) might get change across version. If we perform backup from SQL 2012 and restore on SQL 2014 (using “WITH RECOVERY”, we can see below messages.

64 percent processed.
100 percent processed.
Processed 0 pages for database ‘MyDatabase’, file ‘M’ on file 2.
Processed 13 pages for database ‘MyDatabase’, file ‘M_log’ on file 2.

Converting database ‘MyDatabase’ from version 706 to the current version 782.

Database ‘MyDatabase’ running the upgrade step from version 706 to version 770.
Database ‘MyDatabase’ running the upgrade step from version 770 to version 771.
Database ‘MyDatabase’ running the upgrade step from version 771 to version 772.
Database ‘MyDatabase’ running the upgrade step from version 772 to version 773.
Database ‘MyDatabase’ running the upgrade step from version 773 to version 774.
Database ‘MyDatabase’ running the upgrade step from version 774 to version 775.
Database ‘MyDatabase’ running the upgrade step from version 775 to version 776.
Database ‘MyDatabase’ running the upgrade step from version 776 to version 777.
Database ‘MyDatabase’ running the upgrade step from version 777 to version 778.
Database ‘MyDatabase’ running the upgrade step from version 778 to version 779.
Database ‘MyDatabase’ running the upgrade step from version 779 to version 780.
Database ‘MyDatabase’ running the upgrade step from version 780 to version 781.

Database ‘MyDatabase’ running the upgrade step from version 781 to version 782.

RESTORE LOG successfully processed 13 pages in 0.088 seconds (1.109 MB/sec).

As we can see in the sequence, first files are restored and then recovery process has upgraded version of database. What this step would do? There are few system tables within the database are altered accommodate changes due to new features/fixes etc. These changes are done as a part of “upgrade”. Few things can be seen via profiler but not everything. This version value can be seen in sys.sysdatabases. I have run query in SQL 2012 and SQL 2014 which is same as version seen in restore command (706 and 782)

image

In summary, WITH STANDBY is only supported when both SQL Servers are the same version. We can “see” the database table only by performing recovery. An alternative, if we need read access to the destination databases, is to use replication.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    3 Responses to “Tips and Tricks : Database restore will fail in STANDBY mode”

    1. Aman said

      Hi Balmukund sir,

      It was a wonderful post but i have a doubt on this. I have got the point that when we restore backup of lower version to higher version, restore will fail in standby mode.

      Suppose if we have configured log shipping from lower version of server to higher version, secondary in standby mode. Then log shipping would also fail. Sorry if i have missed anything in post..

      • Aman said

        I have read the post again. Yes, Log shipping would fail if we configure log shipping between different version of server in STANDBY mode.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: