Strategy for standby reporting with different database versions

Question

I am trying to come up with solution for restoring database with one full backup file and then with hourly transaction logs that are going to be created each hour thru FTP site.

So, here is a scenario.

I connect to data provider's FTP site, and get a full backup file first and restore database with 24 transaction logs on a daily basis. So, I will do a nightly run (around 12 am) to update all transaction logs from previous day's data.

I learned that there is an option called Standby, so I tried full back up with standby, but it appears that I cannot do this because database needs to be upgraded. I got this error when I tried Restore Full backup with "Standby" mode:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY confirms that. Now, as standby option is not an option, what should be the way to read data from the database if we are trying to continue doing the transaction log restore everyday (with 24 files)?

I am coming back to the way of restoring database using NORECOVERY for full backup and then for all other transaction log files (because with RECOVERY mode, I cannot restore additional ones later).

But, I need to access database to read data on a regular basis and to query the data. I am not sure if there is a way to read the data (during work hour) when we try to update database with transaction logs. It is not like we could change from RECOVERY mode during work hours to NORECOVERY mode during nightly batch, correct? Do I have to set up like two separate databases (one for restore & other one to somehow grab data from other database). I am not sure that is possible.

What should I do?

Basically, we need to get the updated data from other company thru FTP. After we get the data, developer needs to use it to create reports.

Answer 1

The easiest options are to either upgrade the source instance to the same version as the target (or otherwise ensure the database engine versions are compatible), or perform a restore from a full backup every day to refresh.

For example, say the source was SQL Server 2008 and the target was SQL Server 2012. Upgrading the source installation from SQL Server 2008 to SQL Server 2012 would help ensure a database version match so STANDBY could be used.

Applying changes to a lower SQL Server version will require a bit of development effort.

Answer 2

Jonathan Kehayias' article Database Version vs Database Compatibility Level brings some interesting info related to that error message:

When a database is opened for the first time on a newer version of SQL Server, the database is automatically upgraded by the database engine. If you were to make a backup of a SQL Server 2000 database named ExampleDB and restore it onto a SQL Server 2008 server, the following would be output during the restore process:

Processed 1412 pages for database 'ExampleDB', file 'ExampleDB' on file 1.
Processed 2 pages for database 'ExampleDB', file 'ExampleDB_log' on file 1.
Converting database 'ExampleDB' from version 539 to the current version 655.
Database 'ExampleDB' running the upgrade step from version 539 to version 551.
Database 'ExampleDB' running the upgrade step from version 551 to version 552.
Database 'ExampleDB' running the upgrade step from version 552 to version 611.
Database 'ExampleDB' running the upgrade step from version 611 to version 621.
Database 'ExampleDB' running the upgrade step from version 621 to version 622.
Database 'ExampleDB' running the upgrade step from version 622 to version 625.
Database 'ExampleDB' running the upgrade step from version 625 to version 626.
Database 'ExampleDB' running the upgrade step from version 626 to version 627.
Database 'ExampleDB' running the upgrade step from version 627 to version 628.
Database 'ExampleDB' running the upgrade step from version 628 to version 629.
Database 'ExampleDB' running the upgrade step from version 629 to version 630.
Database 'ExampleDB' running the upgrade step from version 630 to version 631.
Database 'ExampleDB' running the upgrade step from version 631 to version 632.
Database 'ExampleDB' running the upgrade step from version 632 to version 633.
Database 'ExampleDB' running the upgrade step from version 633 to version 634.
Database 'ExampleDB' running the upgrade step from version 634 to version 635.
Database 'ExampleDB' running the upgrade step from version 635 to version 636.
Database 'ExampleDB' running the upgrade step from version 636 to version 637.
Database 'ExampleDB' running the upgrade step from version 637 to version 638.
Database 'ExampleDB' running the upgrade step from version 638 to version 639.
Database 'ExampleDB' running the upgrade step from version 639 to version 640.
Database 'ExampleDB' running the upgrade step from version 640 to version 641.
Database 'ExampleDB' running the upgrade step from version 641 to version 642.
Database 'ExampleDB' running the upgrade step from version 642 to version 643.
Database 'ExampleDB' running the upgrade step from version 643 to version 644.
Database 'ExampleDB' running the upgrade step from version 644 to version 645.
Database 'ExampleDB' running the upgrade step from version 645 to version 646.
Database 'ExampleDB' running the upgrade step from version 646 to version 647.
Database 'ExampleDB' running the upgrade step from version 647 to version 648.
Database 'ExampleDB' running the upgrade step from version 648 to version 649.
Database 'ExampleDB' running the upgrade step from version 649 to version 650.
Database 'ExampleDB' running the upgrade step from version 650 to version 651.
Database 'ExampleDB' running the upgrade step from version 651 to version 652.
Database 'ExampleDB' running the upgrade step from version 652 to version 653.
Database 'ExampleDB' running the upgrade step from version 653 to version 654.
Database 'ExampleDB' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 1412 pages in 14.468 seconds (7.852 MB/sec).

The internal database versions for SQL aren't documented in the Books Online. However, thanks to Paul Randal (@PaulRandal) and his Q&A article in TechNet we have the following list:

SQL Server 7.0 databases have version number 515
SQL Server 2000 databases have version number 539
SQL Server 2005 databases have version number 611/612
SQL Server 2008 databases have version number 655

It seems when you intend to use the WITH STANDBY option that upgrade process should not be needed, or the restore will fail as you have experienced.

I've never noticed that upgrade process myself until Paul White shared the link on another post where I mistakenly related that upgrade with the compatibility level. I thought worth sharing it here too.