database - SQL Server Connection Strings - dot(".") or "(local)" or "(localdb)"

Question

I've recently had to install SQL Server and restore a database to 2 laptops, the first took me a couple of days to figure out, the second I'm still struggling on.

On both I was getting this error here:

A network-related or instance-specific error occurred while establishing a 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 connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'm still getting it on the second.

I did a lot of research and followed all the steps listed on StackOverflow and other sites such as ensuring SQL Server has TCP and Named Pipes running, ensuring that SQL Server was allowing remote connections, and all other troubleshooting steps I could find.

In the end I discovered that I was just entering the connection string wrong on the first laptop. I was trying to use (localdb)MSSQLSERVER2012 (or something like that) as it says to do on so many sites. It started working when I used .MSSQLSERVER2012. I thought they would all point to the same place but obviously not.

I really want to avoid this problem again, and figure out how to get my second laptop set up. How do I know if I should be using .SQLSERVER2012, (local)SQLSERVER2012, (localdb)SQLSERVER2012, etc? Is there a simple way of finding this out using a command line tool like SqlLocalDb? And how is this set up in the first place?

Answer 1

. and (local) and YourMachineName are all equivalent, referring to your own machine.

(LocalDB)instance is SQL Server 2012 Express only.

The other parts are depending on how you install - if you install with an instance name - then you need to spell that instance name out (SQL Server Express by default uses the SQLEXPRESS instance name, while other editions of SQL Server will try to use the default instance without any special name).

So for a "normal" SQL Server installed with all default options on your local machine, use

.    or   (local)     or          YourMachineName

For SQL Server Express installed with all the default settings, use

.SQLEXPRESS    or   (local)SQLEXPRESS     or          YourMachineNameSQLEXPRESS

If you look at the SQL Server Configuration Manager (launch it from the start menu), you'll see:

If the SQL Server entry reads (MSSQLSERVER) then that's that default instance (without any name) - otherwise you'd see the instance name in brackets