I have one machine we've been fighting to do an in place upgrade of SQL Server from 2016 SP2 CU3 to 2017. It keeps failing, but that's another topic. My question is, is there a way I can run the upgrade, but force it to omit upgrading user databases to 2017?

If I can get the system databases upgraded and the server running I'll manually upgrade user databases at another time.

  • You can leave the databases in an old compatibility level, but no, you can't not upgrade them. Depending on why you need them to not be "upgraded", and whether that really means what you are suggesting, compat level may be fine. If a vendor is telling you to (or not to) "upgrade" - tell us exactly what they're saying and we'll help you understand which form of upgrade they're talking about. – Aaron Bertrand Oct 6 '18 at 22:37
  • Assuming 2016 is a default instance, you can try just disabling your existing instance, installing a new named instance of SQL 2017, configuring it to listen on port 1433, and then copy over the user databases and attach them. – David Browne - Microsoft Oct 6 '18 at 22:58
  • The problem is when the upgrade fails (has 6 times now) I have to reinstall 2016, and then I have to restore all dbs from backup. Not a huge deal, but a pia. – Wjdavis5 Oct 7 '18 at 0:37
  • Keep getting: Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes I can do a fresh install of 2017 no problem, but upgrading from 2016 always fails. – Wjdavis5 Oct 7 '18 at 0:38
  • Would doing a backup then a detach work? I'd do a backup just in case since I'm not sure if attaching a db will upgrade it, but worst case, you can always just use the backup (which I'm sure you'll test first) then drop the DBs. – Ali Razeghi May 2 '19 at 16:29

No. As soon as you attach a database to SQL Server, assuming it is compatible, the internal system tables will be upgraded.

While this process is a one-way operation, it takes a few seconds at most.

If you do not wish to upgrade your user databases, don't upgrade to the latest SQL Server.

If you want to keep your databases on a previous compatibility level, that can be done from T-SQL code or inside SQL Server Management Studio, but for all intents and purposes, the internal version of the database will match that of the instance it is installed on.


The database as mentioned in another answer is technically upgraded, but it WILL retain its compatibility level at 2016 level. Having stated that, starting in 2017, Microsoft no longer forces databases to "upgrade": let me be specific, all of the functionalities and all material changes are no longer enabled by default. Rather, they are only activated and change when you change the CL (compatibility level). Therefore, you should be fine because upgrading the Server does not change the CL.

At any rate, if you insist, you can DETACH the database, and then attach it to 2017 when you want. Upon attachment, it'll just upgrade.

  • Please don't confuse upgrades with compatibility levels. Upgrading an instance will upgrade the internal system tables of a database, even if the compatibility level remains the same. This has always been the default behaviour, and did not change with SQL Server 2017. What you are referring to, with features enabled by higher compatibility levels, is unrelated to the original question. – Randolph West May 6 '19 at 23:23
  • @RandolphWest Thank you. But I believe that the question is part and parcel of the more important question which is backward compatibility. Actually, I was correct in my original assertion (and I had not confused CL with upgrade): "Starting with compatibility mode 130, any new query plan affecting features have been intentionally added only to the new compatibility level." : docs.microsoft.com/en-us/sql/t-sql/statements/… – LearnByReading May 21 '19 at 18:48
  • The question asks: "Is there a way I can run the upgrade, but force it to omit upgrading user databases to 2017?". The answer is "No". Compatibility levels have no bearing on this. The internal tables are upgraded when the server is upgraded, end of story. Whether the OP elects to perform the upgrade and use the lower compatibility level is outside of the scope of the question. – Randolph West May 30 '19 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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