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)
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.
Help: SQL Server 