The backend version is not supported to design database diagrams or tables

Question

I'm trying to add a table to my newly created database through SQL Server Management Studio. However I get the error:

the backend version is not supported to design database diagrams or tables

To see my currently installed versions I clicked about in SSMS and this is what came up:

What's wrong here?

Tussis

First Google result ... are you looking at a 2012 database from a 2008 SSMS application? calyansql.blogspot.com/2013/08/… – Equinox

@Equinox I've looked at that too. However I don't see any way to update SSMS to 2012 – Tussis

Answer 1

This is commonly reported as an error due to using the wrong version of SSMS(Sql Server Management Studio). Use the version designed for your database version. You can use the command select @@version to check which version of sql server you are actually using. This version is reported in a way that is easier to interpret than that shown in the Help About in SSMS.

---

Using a newer version of SSMS than your database is generally error-free, i.e. backward compatible.

Thistle

I ran the query and this is what got returned: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 So yea - I see I need to update my SMSS to 2012. However every time I run the exe offered at: microsoft.com/en-us/download/confirmation.aspx?id=29062 I get a message saying no updates are available. I cannot find any way to update SMSS - even though it sounds insanely dumb. – Tussis

Always include the exact error message. You should be able to install SqlExpress 2012 (to get SMSS). You might be able to just do a reconfigure or repair install to make sure SMSS 2012 is running. You also might have 2008 & 2012 and are running SMSS 2008 when you could be using 2012 – Thistle

I launch the exe file. I click "Upgrade from SQL Server 2005, 2008, or 2008 R2". Then I get the message: "There are no updates for SQL Server found online" – Tussis

I think this is supposed to be an informative message, you should be able to continue with the install IIRC – Thistle

Ahh finally I got the 2008 version removed and yes you were right - the 2012 version was already installed. Works now - thanks! – Tussis

Also, if you let Visual Studio create the database for you with Code First then that can cause the error also when using SSMS. – Hunterhunting

I got this error when the sql server was of version Microsoft SQL Server 2014 - 12.0.2000.8 (X64) and I used ssms 2012 version 11.0.2100.60. I only get the error when right clicking a table and then click "Design". So to see which columns the table has, I have to expand the +, which works. Is my ssms confused in some way? – Pectoral

Just install multiple version of SQL server management studio. Sometimes you'll run into a situation where some of your DB's are installed with SQL Server 14, 12, 11 etc. So instead of upgrading your only install of management studio, have multiple installed. – Sapodilla

Yep I had some how loaded a second older version of sql management studio ... Probably should uninstall that. – Depreciatory

It would be nice if v18 had knowledge of the new major version as it says no update is available. Hopefully the updater in v19 will be more future forward. – Edrei

Answer 2

I found the solution. The SSMS version was older. I uninstalled SSMS from the server, went to the microsoft website and downloaded a more current version and now the Database Diagrams works ok.

Plangent

That's odd hat new version of SQL server is released, but SSMS still in preview. – Copland

Mmm strange that the latest stable version includes version 15 for some reason. @Chinthaka Sandaruwan's answer for using the version 19 preview instead of version 18 fixed the problem for me. – Pump

Answer 3

if you got this error in SQL Server Management Studio (SSMS) 18.12.1 in 2022, please download Download SQL Server Management Studio (SSMS) 19 (Preview) and install it, a link is here, download the latest one, this provided link is the current latest version.

Zolnay

It seems to be a bug in the latest stable release. Yes, installing the preview fixes it. – Pump

Answer 4

Just a heads up, Got the same message when I installed SQL Express 2022... Literally Installed SSMS (v18) by clicking the link after Express finished installing. I deleted V18 and downloaded V19, which is still in beta, and it fixed the problem.

Tropo

Answer 5

I ran into this problem when SQL Server 2014 standard was installed on a server where SQL Server Express was also installed. I had opened SSMS from a desktop shortcut, not realizing right away that it was SSMS for SQL Server Express, not for 2014. SSMS for Express returned the error, but SQL Server 2014 did not.

Larimor

Answer 6

I was having the same problem, although I solved out by creating the table using a script query instead of doing it graphically. See the snipped below:

USE [Database_Name]
GO

CREATE TABLE [dbo].[Table_Name](
[tableID] [int] IDENTITY(1,1) NOT NULL,
[column_2] [datatype] NOT NULL,
[column_3] [datatype] NOT NULL,

CONSTRAINT [PK_Table_Name] PRIMARY KEY CLUSTERED 
(
[tableID] ASC
)
)

Osbert

Answer 7

Consider using other design tools like Visual Studio. You can connect to your DB from Visual Studio and use VS design tools which is very easier and faster than writing T-Sql commands.

Unpleasant

Answer 8

So, when attempting to add a diagram to my LocalDB Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22000: ) (Hypervisor) I had to upgrade SSMS to this:

By the way, I know @@version says I am running Windows 10, but I am not. Not sure if that is important to you or not.

Difficult

Answer 9

You only get that message if you try to use Designer or diagrams. If you use t-SQL it works fine:

Select * 

into newdb.dbo.newtable
from olddb.dbo.yourtable

where olddb.dbo.yourtable has been created in 2008 exactly as you want the table to be in 2012

Coaptation