It’s more common than you would expect to have SQL Server installed on your machine without knowing the exact version or edition. With tools and applications installing SQL Server behind the scenes, you may have unknowingly installed SQL Server. In this post, I’ll try to quickly explain some easy ways to investigate this question. This will include:
- Using the Programs and Features control panel tool.
- Understanding and viewing SQL Server instances.
- SELECT @@VERSION
If you’d rather watch the techniques, here is a quick video:
Control Panel
One well-known way of investigating installed versions is the Programs and Features control panel tool (which used to be Add/Remove Programs). In Windows 7, an easy way to get there is to type “Programs and Features” in the search box on the start menu like this:
Your intent here is not necessarily to uninstall anything (although you could). Instead look through the list of installed programs for programs named “Microsoft SQL Server”. For example, in my list I see around ten items that start “Microsoft SQL Server 2008 R2”. Note that it is possible to have multiple versions of SQL Server installled at the same time, so you could also see “Microsoft SQL Server 2005” in the same list.
SQL Server Instances
Even though the control panel is easy and well-known, it doesn’t really tell the whole story well. In order to really understand what versions of SQL Server are installed, you have to understand SQL Server Instances. Each instance of SQL Server operates independently and contains its own collection of databases and settings. There are two types of instances:
Default Instance | a single instance named MSSQLSERVER. You connect to this instance using only the server name (ex: localhost) |
Named Instance | one or more instances. You connect to these instances uing the server name + instance name (ex: localhost\SQLEXPRESS) |
It’s possible have only one instance installed on your machine. But it’s also possible to have many instances. And each of those instances could be a different version of SQL Server!
To understand your scenario, use the SQL Server Configuration Manager tool that installs with both SQL Server 2005 and 2008. On the start menu, expand the “Microsoft SQL Server [YOURVERSION]” menu, and then expand the “Configuration Tools” submenu. Launch the SQL Server Configuration Manager tool. Here is a screen shot to use as an example:
In this example, I clicked on “SQL Server Services” in the left pane. The right pane shows the different services installed on my machine. The ones that we’re interested in are the ones that have “SQL Server” in the Service Type column. You’ll notice that there are two rows with this distinction that are highlighted here. The instance name is listed in parantheses in the first column. In this case there are two instances:
- MSSQLSERVER: the default instance.
- SQLEXPRESS: a named instance, which is the default name of the SQL Server Express instance.
Each instance shows that it is running in its own process. This is useful to know, but the important step is determining the version. To do this, right-click on the instance, and then click Properties. In the Properties dialog, select the Advanced tab. Scroll down to the Version row. Here is what it looks like for the MSSQLSERVER instance on my machine:
The version number for this instance is 10.50.1600.1. In order to translate this, you could search for that version number on the internet. There are several resources that have compiled a list of SQL Server version numbers for you. Here is one example:
SQL Server Version Database on SQLSecurity.com
Resources like this tell me that my default instance is SQL Server 2008 R2 RTM. The other great thing about a version database is that you might find that you’re not using the latest release. For example, I don’t have any of the post-RTM updates installed on my machine.
One final note is that you can see the “Stock Keeping Unit Name” which is a strange way of calling out the edition. In this case my default instance is the “Developer Edition” of SQL Server 2008 R2. The SQLEXPRESS instance on my machine says “Express Edition with Advanced Services”. Sometimes capabilities require a specific edition of SQL Server, so this is sometimes as important as the version information.
SELECT @@VERSION
I think looking at SQL Server Configuration Manager is the best way to truly understand what is on your machine. But there is an easier way if you already know your instances. You can run a simple query. Here’s how:
- On the Start Menu, go to All Programs, Microsoft SQL Server [YOURVERSION].
- Launch SQL Server Management Studio.
- When the Connect to Server dialog appears, log onto the SQL Server Instance of interest. For example, if you’re on the local machine, you can use localhost or localhost\SQLEXPRESS in the Server name text box. Instances were discussed in the previous section of this post. Make sure that the Server type is set to “Database Engine”.
- Click on the New Query button on the toolbar.
- In the query window, type the following query: SELECT @@VERSION
- Press the Execute button to run the query.
- Look at the version text in the Results window.
On my machine, for the LOCALHOST\SQLEXPRESS instance, this is the version information from this query:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
This is perhaps the easiest way of detecting the version of whatever SQL Server instance you’re connected to. But you can see why I covered the control panel and instances first. If you don’t even know what instances are on your machine, you won’t know how to connect to them to run this query in SQL Server Management Studio! One final note, it is possible to have SQL Server installed on your machine without SQL Server Management Studio. If that is the case, you can still use the previous methods for determining your version, but you’d have to install the SQL Server Management Tools to run a query. This will most likely be covered in a later post.
Other resources on this topic:
- How to find out the Sql Server version and service pack (SQL Server Curry Blog)
- SQL SERVER – Get Server Version and Additional Info (SQLAuthority Blog)
- How to identify your SQL Server version and edition (Microsoft Support)
- @@VERSION (Transact-SQL MSDN Documentation)
hi bro…can you take a look at my sql server 2008 problem
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/729ba93f-9814-4384-9d05-fe830086fa15?prof=required
my problem is…i installed a clean version of microsoft sql server 2008 enterprise edition on my pc…but somehow when i connect..it connects to a microsoft sql server 2005 instance..how can i change the instance to 2008 version??
I would guess that you have multiple instances installed and you’re not connecting to your 2008 instance. Try “.” without the quotes to see if you have a default instance. Then run SELECT @@VERSION. Then try “.\SQLEXPRESS” to see what @@VERSION tells you about your express instance. The easiest way to prove you have 2008 installed is to use the SQL Server Configuration Manager. Watch the video in this post and it should walk you through exactly what to look for.
after reading your post and watching your video twice..i started to understand a little more…i think i don’t have the default instance on my pc..that’s why. any idea how or where i should get it??…i remember installing every single thing during the setup process
The default instance is a selection on one of the setup pages. If you’re installing SQL Server Express, then “Named Instance” is automatically selected with a a named instance of “SQLEXPRESS”. But you can change this to a default instance, even for express. If you’re installing another edition, than the default instance radio button is the selection unless you already have a default instance installed. But note that SQL Server Configuration manager will tell you whether you have a default instance. It will show a SQL Server service type with the name (MSSQLSERVER) next to it. Hope this helps.
thanks for the reply..i think where’s my problem is now..there’s actually a bug during my installation process as can be seen here
http://www.kendalvandyke.com/2009/01/installing-sql-express-2008-as-default.html
If that’s the case, then you can use the workaround at that link and you should be able to get your default instance up and running. I had a free machine (Windows Server 2008 32bit), and I tested installing a default instance of EXPRESS (SQL Server 2008 R2 Express). It worked for me. I wonder if this has been fixed or if there are more variables going on to reproduce it. Regardless, I hope you can now either: a) Use the named SQLEXPRESS instance it installed for you, b) use the work around to create a new named instance named MSSQLSERVER as your link suggests to work around the problem and create the default instance explicitly.
Roth,
What are the differences between “Instances” vs “databases” within the SQL Server Management Studio 2008 R2? I have the default System DBs (master, model, msdb and tempdb) and the other added DBs. I though those “added databases” were the instances.
Thanks,
Chickie
Hello handsome
Can you please tell me how to check installed features in MS SQL SERVER 2008 r2
I’m not sure if there is an easier way, but if you go to “Programs and Features” and select your SQL Server 2008 R2 program to make changes, you should be able to bring up the setup wizard that shows the list of features that are checked or unchecked. You can then make changes to your installation or just use that for your own knowledge.
it worked for me and thanks for the worthful advice….
where do i find the exe file of sql server.. i mean its has been installed and i can see it listed in my programs menu but then i am not able to open it.There is an import and export data wizard and a configuration tools folder..when i try and open the installation center inside the configuration tools folder and install it gives me a pop window asking me to “Browse for SQL Server 2008 R2 installation media “.. what is that? Please help.
Usually when you’re asked to browse to the SQL Server “media”, it means that you installed once but now when you’re trying to add features (or remove), it can’t find the setup program to complete those actions. I think I’ve seen this before if I installed it over a network share. I see your other comment too. I think that is the best route of trying to install it again from the internet download. Unfortunately, you seem to be having problems doing that too. I’ve installed on Windows 7 many times. Did you make sure to pick the right platform (32bit or 64bit) for your machine? There is a service pack 1 for Windows 7 that you could make sure you’ve installed (I don’t think it is necessary though). From the Start menu, go to or search for “Run”, and from the Run dialog, type winver, and press enter. It should tell you whether you have SP1 installed or not. Finally, if you’re installing the package with *just* management studio, I would suggest trying to install the package that has the Database Engine + Tools (i.e. Management Studio). Hopefully one of these suggestions helps.
Thanks a ton for your suggestion.. will try again and let you know.. 🙂
one of my friends suggested me to install sql server management studio . but whenever i download and install it.. it gives me error saying that this version is not supported by the Windows OS installed here. i have Windows 7 on my computer… Please reply soon.
Hi,
Is it possible that I can have only one instance and that too a named instance of SQL server 2008 on a machine .Ex:- While installing , I give a name to the instance , like MSSQLSERVER_MYNAME . Can I have this kindof scenario.
Regards.
Yes. This is, in fact, the scenario that happens by default when you install SQL Server Express on a machine. You get a single named instance, SQLEXPRESS, and no default instance. You can change that to a name of your own choosing. Or you can make SQL Server Express your default instance. It is very flexible. Thanks.
We have SQL Express was custom installed by mistake – the instance name is the same as the computer name – Can we rename the instance to MSSQLSERVER without reinstalling?
Jason,
How do you recommend tracking sqlserver instances in a corporate environment ? Our dba team finds that sometimes our Windows SA will install and deploy sqlserver, and we find out about them only when there is a problem. Is there a tool that will track sqlserver instances ? I have found that sqlsvr studio will report clients, which I don’t need Thanks
HII ROHIT HERE
The database ‘D:\POS\POS18\POS\DATABASE.MDF’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘D:\POS\POS18\POS\DATABASE.MDF’. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)
I AM GETTING THIS ERROR PL HELP ME TO SHORT OUT THIS PROBPLEM.
Very useful. Thanks!
i’m getting error 916 while attaching the database what shall i do?
Great Article – very useful info. Thanks for sharing!
God bless you ! it worked for me ! 🙂 Keep writing !
Thanks! best explaination i’ve seen yet…
g.
Thank you very much!
Hi,
i have recently installed sql server 2008 r2, but unfortunately i dont remember the password i gave for the the authentication mode while installing. The reason i gave a different password is because i dont have any password set up to access my system. Any suggestions as to how i can retrieve the password given would be really appreciable.
Thanks&Regards,
Nagesh.
this short article has helped me a lot! thanks!
Hey there just wanted to give you a quick heads up.
The text in your article seem to be running off the screen in
Chrome. I’m not sure if this is a format issue or something to do with browser compatibility but I figured I’d post to let you know.
The design look great though! Hope you get the issue solved soon.
Many thanks
http://www.irmaosfranciosi.com.br/wiki/index.php?title=Promotional_Flash_drives
HI,
my problem is…i installed version of microsoft sql server 2008 enterprise edition on my pc.. happily am working on that. but, when i am attaching sample data base, its giving the following error.
——————
An exception occurred while executing the T-sql statement or batch.
The database ‘adventureworks’ cannot be opened beacuse it is a version 655.this server support version 654 or earlier. a downgrade path is not supported. could not oened new database ‘Adventureworks2008’
.create database is aborted
———————————————————
I am new to SQL.Can any one pls give the solution..
Great job!
Wow, amazing blog layout! How long have you been blogging
for? you made blogging look easy. The overall look of your website is excellent,
let alone the content!
This piece of writing offers clear idea for the new users of blogging,
that actually how to do blogging and site-building.
Good Afternoon, I am currently trying to silently install / package software. The software i am trying to install also installs SQL Express 2008 in the background and configures it for use with the software. I am now trying to break these installs down and package them individually. is there anyway to capture the current SQL 2008 settings and deploy them as a configuration file?
Just to make clear the current install of sql is packaged behind an install and i have no clue as to how its configuring it. I can only go in to SQL after install and see its configuration.
hope you can help???
Jonny
hi,
In my configuration manager i have mssqlserver instance,no more instances.
When i installing sql server i gave server name ‘.’ and using sql server authentication.
But when i connecting to my database it gives error
Error:The TCP/IP connection to the host ., port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.
Is this error because of missing another instance like ‘sqlexpress’?Help me pls
Generally I do not read post on blogs, but I would like to say
that this write-up very compelled me to take a look at and do so!
Your writing style has been amazed me. Thanks, quite nice article.
I have read so many articles regarding the blogger lovers however this
post is in fact a pleasant piece of writing, keep it up.
Hey There. I discovered your weblog using msn. This is an extremely smartly written article.
I’ll be sure to bookmark it and return to learn more of your useful info.
Thank you for the post. I’ll certainly comeback.
Greetings from Carolina! I’m bored to tears at work so I
decided to browse your blog on my iphone during
lunch break. I really like the info you present here and can’t wait to take a look when I get home.
I’m shocked at how quick your blog loaded on my mobile ..
I’m not even using WIFI, just 3G .. Anyways, fantastic
blog!
Determining What Microsoft SQL Server Edition is Installed (in Spanish):
http://www.sysadmit.com/2015/01/mssql-saber-la-version-de-sql-server-instalada.html
Hello Brother, my problem like this… Cannot Connect to Sqlexpress.
Additional information
Anetwork-Related or instance-specific error occurred while establishing connection to sql server.
The server was not found or was not accessible. verify that the instance name is correct and that sql server is configured to allow remote connection.(Provider:Name pipes Provider, Error 40-Could not open a connection to sql server) (Microsoft sql server error 53
)
Hey There , There IS No SQL Service On That List And I HAve A PRoblem With Use SQL On Visual Studio. Can U Help Me pls
this is still VERY helpful..
thanks mate!
Rich
I think the admin of this web site is genuinely working hard
in support of his web site, since here every stuff is quality based data.
Was Zeit einige Jahre schon in mehreren Europäischen Ländern stattfindet,
ist jetzt auch in Deutschland eingesetzt, fahrendes Mobile Geschwindigkeitskontrolle
mit anonymem Fahrzeuge.
In Berlin können Gäste die Stadt beim Jogging erkunden und in hunderten Restaurants leckere und gesunde Gerichte aus aller Welt
probieren.
Wenn der Kopf Bescheid weiß, man also etwas „Schlechtes ablegen möchte (zu schnell essen) bzw.
Es geht leider immer nur sehr pauschal darum dass Bewegung
gut tut, eher selten darum welche Art von Bewegung.
Die Zahl der Kalorien sagt also NICHTS darüber aus, wie gesund die
Ernährung ist.
Oh Wunder- zum Anfang wahrscheinlich nicht, aber zum Ende des Monats wird der Blutdruck nach dem Gehen wesentlich geringer sein als
vor dem Ausflug.
Die Wahrscheinlichkeit eines Herzinfarkts oder einer Verengung der Arterien der Arme und Beine nimmt zu.
Mit einigen kann zum Beispiel zum Abnehmen ohne Sport eine Mahlzeit am Tag ersetzt werden, sie existiert oft in Form von Trinken oder Shakes.
Mit unserem Eigenmarkensortiment und unserer Obst- und Gemüseauswahl setzen wir auf
gesunde Frische und hochwertige Qualität.
Thank for your sharing. It is very helpful to me because I have no idea to find how many instance have been created in my local PC.
how many computer can i install sql server 2012 enterprise on !!!!
i have a license
Another good example:
SELECT
SERVERPROPERTY(‘MachineName’) AS ComputerName,
SERVERPROPERTY(‘ServerName’) AS InstanceName,
SERVERPROPERTY(‘Edition’) AS Edition,
SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel;
GO
Using sql server express : finally 1 and only tool in market to manage (backup automation, scripting automation , index and shrink automation), monitor , check health – with free web tool , try for 4 month full feature, Download now :
https://sites.google.com/view/sqlplanner