The database cannot be opened because it is version 851. This server supports version 782 and earlier. A downgrade path is not supported

Question

I am trying to attach database file in SQL Server. I am getting the below error. The same error I am getting while trying to attach database from Visual Studio. I am using Visual Studio 2013 and SQL Server 2014 Management Studio.

My connection string:

<add name="Sample"  
     connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\M\Desktop\SampleApplication1\App_Data\Sample.mdf;Initial Catalog=SampleDBContext;Integrated Security=True;" 
     providerName="System.Data.SqlClient"/>

Error:

The database 'sample' cannot be opened because it is version 851. This server supports version 782 and earlier. A downgrade path is not supported. Could not open new database 'Sample'. CREATE DATABASE is aborted.

and here is the default SQL Server instance is set in Visual Studio.

Fachanan

This answered my question #26347147 – Antony

Answer 1

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server down to an older version - the internal file structures are just too different to support backwards compatibility. Obviously, your database is from a SQL Server 2016 pre-release (internal version: 852) while the engine you're trying to attach it to is SQL Server 2014 (internal version: 782).

You can either get around this problem by

• using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances

• otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool

• or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

Misguide

+1, instead of attaching this DB, I will try to generate the database script as you suggested in 2nd point. Actually this Database was created on godaddy server. Thanks marc_s. – Fachanan

How do you update this, and why is it different on machines? All I did was install vs2015 on both machines. – Xylon

Answer 2

I was having this issue while following an old Pluralsight MVC4 tutorial.

The course was asking me to use v11.0, but I was running a 2015 version. I opened up the SQL Server Object Explorer and looked at the different versions of LocalDB, and grabbed the name of the one that had my database in it (right-click "Rename").

I then pasted the name in the Add Connection window, under Server Name. Clicked Refresh, and voila, there it was. Hope this helps anyone out there.

Sewellel

Answer 3

This Microsoft link may resolve your problem if you are still running Visual Studio 2015 and seeing this error - the answer above is part of the answer. (megamaiku) The "generate scripts" answer from Mark_S will work assuming you have no trouble mounting/accessing the database. If I recall the SQL Compare is rather expensive but that is a great solution, again, assuming you can mount the database created by your Visual Studio application.
A similar issue arises with Visual Studio 2017 and SQL Server 2017 except you see 852/856 as the version problem.

After moving the database, change your web.config connectionString to match your server; (localdb)\MSSQLLocalDB or possibly localhost depending on what you are running (SQL 2016/2017/2019).

<add name="DefaultConnection"
     connectionString="Data Source=(localdb)\MSSQLLocalDB;myDB.mdf;Initial Catalog=MyDBName;Integrated Security=True"
     providerName="System.Data.SqlClient"/>

OR

<add name="DefaultConnection"
     connectionString="Data Source=localhost;myDB.mdf;Initial Catalog=MyDBName;Integrated Security=True"
     providerName="System.Data.SqlClient"/>

An old article explains how to pull the pieces from the database one at a time and if you're lucky and it still works (it did a decade ago - separate and then put the parts back together), you may be able to get what you need - a newer version restored to an older version. I am unable to find a reference to the trick but rather than scripting your entire database, there is a way to restore down by restoring the header (restore headeronly), then once the header is in place on your server and the permissions and so on are matching, you can restore that database from the newer backup.
However, the generate script is less convoluted and more popular.

Correy