Detach Default Database

[jv6886]

I'm a relative newbie to the wonderful world of SQL Server 2005. I have several small databases on my server (nothing very important). The other day I detached a database and then when I tried to log in again I got a "Cannot connect to ..." message. I asked a colleague about it, and he said that it was probably because I detached my default database. He wasn't sure how I could fix it.

Does anyone have any ideas about how I can connect to the server again?

If that is not possible, how can I delete the server and install a fresh one?

Thanks,
wcprog

 

[ptheriault]

Are you trying to log into some web site or application you own? You need to login to SQL Server Management Studio and attach your database.
1.Start Management Studio
2. Connect to your server in Object Explorer
3. Right click on Database
4. Select Attach
5. Browse to the location of your database. (It will most likely be something like this.
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\*.mdf
6. Select your db and attach it.

You can't detach a system database so you won't need to re-install SQL server.

 

[jv6886]

ptheriault,

Thanks for your reply.

No, I am not trying to log into any web sites or application. I am using SQL Server Management Studio. When I try to connect to the server, I get the above-stated message "Cannot connect to <Server name>".

I can connect just fine to another named server instance that I have... just not the server in question.

Thanks,
wcprog

 

[ptheriault]

Have you checked your login info and are you sure the services are running?

 

[jv6886]

I would think that the services would have to be running, because I am able to connect to another named instance with no problems.

Same with my login info since both servers are running on Windows Authentication.

Thanks,
wcprog

 

[ptheriault]

Each instance of SQL has it's own service. You might want to check it in Windows services.

 

[Catadmin]

If you have detached your default database, the simplest solution is to have someone go into the properties of your login and change your default database to a currently existing DB and make sure you have access granted (just public will do) so you can log in.

If that doesn't work, then his assumption of what the problem is was wrong.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jv6886]

Catadmin,
The problem is that the server is on my local machine, so I am the only admin.

ptheriault,
All services are running.


Here's what happened. I detached the database. When that was finished I immediately disconnected from the server. A few minutes later, I tried to reconnect, and then I received the above message.

Thanks,
wcprog

 

[ptheriault]

I am going to assume that you are using you local account to login to the instance? Do you know the 'sa' password. If you could login as 'sa' then Catadmin suggestion will work. You might have to edit the server registration properties in Enterprise manager.

Right click on the instance name and select properties.
Change your login from Windows authentication to SQL Server.
Enter in your sa login info.

It should work.

 

[Catadmin]

And if you don't log in each time you go into EM, then just delete the SQL Server registration from your list, re-add it and when re-adding, tell it to use a SQL Server login. This will force EM to ask you for name & pwd when you try to connect to the server and you can enter in the SA login & pwd at that point.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jv6886]

Thanks to both of you for your suggestions.

Unfortunately when I initially installed the server, I didn't install it with the dual-mode login. I opted for only the Windows Authentication, so I didn't have an sa account.

I ended up uninstalling the server and reinstalling it and restoring my backup databases.

Lessons learned:
* Don't detach a database without making sure it isn't my default database. Whether or not this was the actual cause of my problem it's probably a good idea to check.
* Always install with dual-mode login.

Thanks, once again for your help.
wcprog