I have inherited a vendor product. It’s currently very old, having been installed in 2013 and never updated in the 11 years since. The database server it’s using for its 3 database is SQL Server 2008 R2. The databases are all required, and are of various sizes (currently 115GB, 67GB and 20GB approximately).

There is a daily backup-and-restore of each of the 3 vendor databases to a reporting database server, which is SQL Server 2016. Further copies of those database are made (again via backup and restore) to at least 3 other SQL Server 2016 servers and used in various offline processes. All of these reporting servers have many, many other databases on them and are used widely throughout the company by various groups.

All servers are on-premise. The daily / end-of-day backups are sufficient; i.e. there is no requirement for real-time synchronisation of the databases.

We are currently in the process of upgrading the vendor product. Doing so requires that the database server be upgraded to SQL Server 2019 (it could be a more recent version, but this is where we have landed).

I know that this will mean that the backup and restore to the SQL Server 2016 reporting server will no longer work, as it’s not possible to restore to an earlier version of SQL Server.

I was hoping to get some advice on other options. We have given consideration to a few, but haven’t tried anything or run any proofs-of-concept. I really wanted to see what the community advised or if we were missing anything,

  1. Transactional replication - discounted since the vendor product has over 300 tables without primary keys, which I understand means that they can’t be replicated this way. Changing the underlying tables isn’t possible.
  2. Snapshot replication - concerns here over the snapshot size and performance of this method, given the database sizes in question
  3. Upgrading the target reporting server to SQL Server 2019 and continuing to use backup and restore. This option is currently our fallback but will involve several upgrades to the servers that the original database is subsequently restored to, and will introduce complexity and risk to the project that I’d prefer to avoid.


  • "the vendor product has over 300 tables without primary keys" - That's pretty terrible, they probably should fix that (in fact, you should find out if they did fix that in this upgrade). In any case, do you guys use all 300 of those tables for reporting purposes? Are you allowed to create your own separate objects in the source server, such as Indexed Views? Also, FWIW, SQL Server 2016 has been out of mainstream support for a few years now, and will be completely unsupported in about 2 years, so you guys should probably look to upgrade those reporting servers soon.
    – J.D.
    Commented Feb 20 at 13:24
  • Agreed on the tables without primary keys. I've followed up with the vendor to check the new version but every time we've asked about the upgraded database, it's been pretty much 'as you were' and no changes from the older version.
    – fiveeuros
    Commented Feb 20 at 13:58
  • I can’t say whether or not any, all or none of the affected tables are used in reporting. It’s a bit of an unknown quantity as you can imagine after 10 years; there are a number of published and controlled reports that we can analyse with confidence; but there are also an unknown number of ad-hoc reports that will be harder to track down. It’s a bit of a mess, and honestly the rationalisation and consolidation of reporting feels to me like it should be a project in itself. Not something I want to tack on as part of the upgrade.
    – fiveeuros
    Commented Feb 20 at 14:06
  • 1
    "no, we won’t be able to touch the source database or it will void our warranty with the vendor." - Positive on this?...I've worked with quite a lot of 3rd party vendor databases and (surprisingly) they're ok with changes as long as they're only new objects being added and not any direct changes to their objects. That being said, if you really constrained to absolutely 0 changes on the source server, then all Replication options go out the window too since they create objects in the source server / database.
    – J.D.
    Commented Feb 20 at 14:52
  • 1
    Thanks - have an email out to the vendor company to check and verify this, and to discuss options.
    – fiveeuros
    Commented Feb 22 at 10:10

1 Answer 1


Short Answer

Option #3. Upgrade/replace the target(s) to SQL 2019.

Long Answer

We run into a similar issue whenever we first implement a new version of SQL Server. We have a number of secondaries, whether they are used for reporting, or analysis, or automated restoration of databases to run DBCC CheckDB against them. Sometimes the data gets to them via replication, sometimes log shipping, and sometimes old-fashioned backup/restore.

The way we handle it is to ensure that we upgrade -- or at least spin up a second server/instance in parallel -- our various secondaries at the beginning of our journey to a new SQL version. So, for example, we recently built a SQL 2022 instance to receive restores for offloaded DBCC CheckDB validation before we installed any other instances of SQL 2022.

In some cases, like our DBCC CheckDB instance, we just built a new SQL 2022 instance and moved everything over to it. And in others, like some of our data warehousing, we built a new SQL 2022 warehouse instance and ran it alongside our existing SQL 2017 warehouse instance until such time as we get everything moved over to it, and we can shut down the SQL 2017 warehouse instance.

You say that Option 3 is your fallback, and will introduce complexity and risk. I get that. But you're going to have to tackle that complexity and risk eventually. Maybe not for this upgrade, but for some other one in the near future. You're not really changing the amount of complexity and risk -- you're just moving it around a bit. And I maintain that getting it done now will not only make this upgrade go more smoothly, but it will also lay the groundwork for making the rest of your upgrades to SQL 2019 easier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.