5

I'm currently hosting a database on the server MyComputerName using SQL Server Express 2014.

I want to move the database to another computer, so I installed SQL Server Express 2014 on it but that computer only has the instance ThatComputerName\SQLEXPRESS. When I try to connect to just ThatComputerName it throws the error

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) (Microsoft SQL Server, Error: 2)

However, I can't enable remote connections on ThatComputerName because I can't even connect to that instance.

I don't remember what I did on the previous computer to allow me to connect both to MyComputerName and MyComputerName\SQLEXPRESS, how can I create that instance on the new computer? Do I have to reinstall?

Improve this question
8
  • Hold on. Am looking up something. The default instance name for SQL Server Express is SQLExpress, so you installed it correctly. Chances are you did not set up the protocols it is listening on correctly.
    – Solomon Rutzky
    Dec 1, 2015 at 20:15
  • What do you mean by "I can't enable remote connections because I can't even connect to that instance"? You need to remote desktop to that other machine, enable TCP/IP, and then connect remotely. Or are you saying that while remoted desktopped into ThatComputerName you are not able to connect to the SQL Express instance? Something doesn't add up here.
    – Solomon Rutzky
    Dec 1, 2015 at 20:29
  • I've enabled TCP/IP and turned off firewall. But to enable remote connections, I need to connect to the ThatComputerName and change the properties in SSMS, but I can't connect to the instance in the first place on the machine that I want to host the DB.
    – Charles Clayton
    Dec 1, 2015 at 20:43
  • please see my answer. You don't need to do anything in SSMS. You just need to restart the service, assuming you enabled TCP/IP for the "instance" and not just for "Native Client".
    – Solomon Rutzky
    Dec 1, 2015 at 20:48
  • I am still confused by "but I can't connect to the instance in the first place on the machine that I want to host the DB". Do you mean that you are physical on ThatComputerName via Remote Desktop, AND while on there, you cannot access the local instance of SQLEXRESS? You can't even run SQLCMD -Q "SELECT @@SERVERNAME;" from a command line? If that is the case, are you sure that the service is for that instance has been started?
    – Solomon Rutzky
    Dec 1, 2015 at 20:51

1 Answer 1

Reset to default
5

You cannot change the Instance Name after it has been installed. But you can uninstall that SQL Server instance and reinstall as a DEFAULT instance. Ideally you should make a backup of the server, then restore the backups to the new server.

You can technically detach databases and attach again after the reinstall, but that includes the risk of losing your detached databases. See Aaron Bertrand's post:

https://blogs.sentryone.com/aaronbertrand/bad-habits-file-backups/

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

All default instances use the MSSQLSERVER internally, but they just do not make it visible to you.

Improve this answer
9
  • That's what I was suspecting, however, I don't ever remember doing that on my original computer. Oh well, thanks for your help!
    – Charles Clayton
    Dec 1, 2015 at 19:57
  • 1
    Or back them up and restore them (I talk about why detach/attach is so dangerous as a backup mechanism here).
    – Aaron Bertrand
    Dec 1, 2015 at 20:22
  • @crclayton This is not true. SQL Server Express uses SQLExpress as the default instance name, not MSSQLSERVER like all of the other editions do. So you did install it correctly.
    – Solomon Rutzky
    Dec 1, 2015 at 20:26
  • @srutzky - crclayton wanted his database to show ComputerName instead of ComputerName\SQLEXPRESS. That is how I set up SQL Server Express a few years ago to be a 'local instance' and not SQLEXPRESS. Has that behavior changed?
    – RLF
    Dec 1, 2015 at 22:23
  • 1
    @srutzky I think that's a translation error of some kind. I've asked for the page to be corrected.
    – Aaron Bertrand
    Dec 2, 2015 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.