How to connect to a local database in SQL Server Management Studio?

Question

I have downloaded the SQL Server Management Studio (SSMS) 2016 to recover a huge .bak file which is an old backup of a database. But first of all I need to connect to a DB Server.

After install, I execute SSMS and it asks me to connect to a DB Server. I don't have any and I saw that it is possible to create a "local" db by typing only a dot '.' for Server Name and use Windows authentication.

It is not working; how to do it?

Linea

You need to install local SQL server instance. It doesn't appears magically if you just installed only SSMS and use dot or whatever as server name. – Quaff

Answer 1

You need to download and install SQL LocalDB. It is a special edition of SQL Server that does not allow remote connection and supports windows integrated authentication only. It is a simple one click MSI install so it is easy to deploy.

To download and install SQL Server 2016 Express, go to SQL Server downloads. LocalDB is a feature you select during installation, and is available when you download the media. If you download the media, either choose Express Advanced or the LocalDB package.

The above means that now you have to download and run a small downloader (cca 5 MB), select LocalDB (44 MB) and the Microsoft downloader will download MSI package to the folder you select. Then just run MSI package and LocalDB gets installed.

Motive

OK but I have to download also the SQL Server 2016 Express ? I already have SSMS, or maybe you have a direct link to Sql LocalDB (msi) ? – Linea

They have changed the downloading system since LocalDB 2014, now you have to download a small Visual Studio like installer (5.1 MB) and you have to choose desired edition then. – Saintmihiel

Once installed, create a temp db by entering the following in a command prompt SqlLocalDB.exe create "temp" -s – Murillo

Microsoft's installation information for LocalDB is now learn.microsoft.com/en-us/sql/database-engine/configure-windows/… . As noted in the answer it "does not allow remote connection and supports windows integrated authentication only", so an attempt to connect on localhost or 127.0.0.1 etc. will also fail. – Contradistinguish

Answer 2

1. Open SSMS -> Connect
2. Server name: (LocalDb)\MSSQLLocalDB,
3. Choose Windows Authentication.

For a reference:

Bhakti

You shouldn't have to install anything else.. this is the correct answer! thanks! – Electrotonus

You can also see this name of the sql server to connect to by viewing Server Explorer within Visual Studio. The name "(localdb)\MSSQLLocalDB" was listed there for me – Comyns

This is only correct if you installed everything (all options) locally. Corporate "locked down" installations of Management Studio might not have a localdb instance available. – Alecalecia

@Comyns Easier to use View > SQL Server Object Explorer in Visual Studio. – Kristof

Thank you, I was typing the same (localdb)\mssqllocaldb but was not succeeding because I was typing in lower case! – Priester

Sweet. No installation needed. – Congeneric

If you're a newcomer like me and you're surprised where this name comes from, I've been actually able to find it in appsettings.json (at least in my starter .Net Core 6 project). There must be a line like this in the ConnectionStrings field: "MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-da5ebc14-b675-4050-b406-f83dc1bd1a7a;Trusted_Connection=True;MultipleActiveResultSets=true". – Melli

This is way old but gold and works like a charm. Thank you! – Err

Answer 3

In my case, solution was using my notebook name and SQLEXPRESS like DESKTOPXPTO\SQLEXPRESS

Interdigitate

Answer 4

To recover your Dump, you need one server where your dump will get uploaded. For this you need one MS server. you can connect to server using username and password. After connection to server you can create a DB in which you want the dump to get imported.

If your SQL dump contains create Database statement, then you don't need to create one.

If its possible to open SQL dump in text file, you can see Database name required to create or its already have statement to create DB.

Loggins

Answer 5

None of the options mentioned up until now worked for me. However, I found the solution to this problem. Here's what I had and what I did:

Open Visual Studio Installer. Then go to:

• Visual Studio Community 2022
• Modify
• Individual Components
• Type "SQL" in search bar
• Locate "SQL Server Express 2019 LocalDB"
• if unchecked, check it
• in the bottom-right corner click "Install".
• if it was already checked - uncheck, delete, and reinstall it again.

Hope it works for you just like it did for me, good luck!

Conduce

Answer 6

I was able to connect to local host in ssms ,after downloading SQL server developer.

As mentioned in - https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver16

Prerequisites To complete this quickstart, you need the following prerequisites:

Install SQL Server Management Studio. Install and configure a SQL Server instance

https://www.microsoft.com/en-in/sql-server/sql-server-downloads?rtc=1

Speechmaking