Cannot connect to (local) - how can I copy databases to another computer?

Question

Cannot connect to (local) - how can I copy databases to another computer?

webskater

SSCarpal Tunnel

Points: 4214
More actions
April 17, 2016 at 4:06 am

#400029

Yesterday, everything was fine. This morning - open SQL Server Management Studio, the Connect to Server window appears, I - as I do every day - click 'Connect' but, today, nothing happens.
Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
Username: MyComputerName\MeAsUser (grayed out)
Password: empty (grayed out)
All exactly the same as every day for the last 5 years.
Click 'Connect' and it just sits there doing nothing - for many minutes before I give up and click Cancel.
Given I cannot open SQL Server Management Studio - how can I copy 'my databases' - the tables, stored procedures, user defined functions etc. to another machine? I am not worried about the data in the tables as they are test databases?
I have tried stopping and starting the SQL server (SQLEXPRESS) and SQL Server Browser services.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Just in case you've not already tried...

Since this isn't a production box, do the "normal" thing... something may have gone haywire with Windows or SQL Server express. Try rebooting the machine where the databases live. If that machine isn't the same machine as where you're running SSMS from, try rebooting the machine where you're running SSMS from.

If that doesn't fix it, see if a different machine using SSMS can get to the database(s).

If that doesn't help, then to copy to another machine to see if that fixes things, stop the services and copy the related .MDF and .LDF file(s) for the database(s) in question to another machine and try attaching the database(s) there.

If none of that works and you 1) don't have a viable backup and 2) you have nothing checked into a source code manager and 3) you haven't even saved the code to local disk, then you might be toast.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 2

Thanks for your reply.

I do have the production database on a different server ... I can get to that and script out the tables, stored procedures, etc. if I have to. But, at the moment, I can't effectively develop as SSMS sees nothing. It doesn't even produce any sort of error, just sits there doing nothing. Haven't got another machine here with SSMS on to see if a different machine will open the databases.

I'm doing system restores at the moment, going back one step at a time ... but some of these are not working. Happy days.

Any thoughts on whether installing a later version of SQL Server might do the trick?

When you say stop the services, copy the .mdf and .ldf files and, on a different machine, attach them. How do you attach a .mdf and .ldf file so that SSMS opens it as a database?

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

SSMS doesn't "open" anything as a database. SSMS is just the client that lets you look at what is on the SQL Server instance. It's the instance that manages and does all the work around maintaining the databases. You can query and control that instance without ever using SSMS. SSMS just makes it easier, providing a GUI to access all the stuff. Don't confuse the two.

If you've successfully attached the database using the T-SQL (which is all that SSMS does under the covers) for the attach process, then, when you connect in SSMS to the instance to which you attached that database, it will be there. Use the command illustrated here to perform this action.

It sounds like there's something wrong with the Express instance you're running, not with SSMS itself. I'd suggest getting the error log, which is just a file. You don't need SSMS to read it. It's located here: Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG (you'll have to substitute your instance name).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Is the SQL Server service on your machine running? Not Express, from the server name you listed it doesn't look like you're connecting to the express version of SQL (usually would be (local)\SQLExpress for that)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass