SQL Server Standby Reporting Strategy with Different Database Versions

restoresql serverstandby

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:

enter image description here

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.

Best Answer

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.