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?
1 Answer 1
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.
-
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– Charles Clayton2015年12月01日 19:57:26 +00:00Commented Dec 1, 2015 at 19:57
-
1Or back them up and restore them (I talk about why detach/attach is so dangerous as a backup mechanism here).Aaron Bertrand– Aaron Bertrand2015年12月01日 20:22:47 +00:00Commented Dec 1, 2015 at 20:22
-
@crclayton This is not true. SQL Server Express uses
SQLExpressas the default instance name, notMSSQLSERVERlike all of the other editions do. So you did install it correctly.Solomon Rutzky– Solomon Rutzky2015年12月01日 20:26:29 +00:00Commented 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– RLF2015年12月01日 22:23:08 +00:00Commented 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– Aaron Bertrand2015年12月02日 02:30:20 +00:00Commented Dec 2, 2015 at 2:30
Explore related questions
See similar questions with these tags.
SQLExpress, so you installed it correctly. Chances are you did not set up the protocols it is listening on correctly.ThatComputerNameyou are not able to connect to the SQL Express instance? Something doesn't add up here.ThatComputerNamevia Remote Desktop, AND while on there, you cannot access the local instance of SQLEXRESS? You can't even runSQLCMD -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?