Can't use DB connection after detach of DB

Question

Post reply

Can't use DB connection after detach of DB

Quinn-793124

SSCrazy

Points: 2785
More actions
October 29, 2009 at 9:34 am

#206090

I needed to move a database off of the c:\ drive on the server. I found a process on our GIS vendors web site that had Kill connections, backup, Stop their process, detach, move DB, attch DB, and start GIS service again. I wasn't sure about how to kill the connections and when I was at the detach screen it had the kill connection option which I figured would cover that and after I did the detach I can't get back into the database connections in SSMS. I can attach to the analysis server DB's but not the regular DB's. What did I do and how can I fix it?
Starting to panic.
Thanks
Quinn

Viewing 13 posts - 1 through 12 (of 12 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

you might have been connected to that particular database, or it's your default.

Try opening a new query window, connecting to your instance, choosing master as a specific db.

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 2

Thanks Steve

I was able to get in changing the DB to master. I can see all of my DB's as well. Is it safe to try to attach the DB that I moved?

Quinn

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Should be fine. Glad that worked.

I think that the problem was that you were using that database and when you clicked, it disconnected you. Your session went, and that included SSMS, so it flaked slightly. Then when you went to reconnect, you had the detached db as your default.

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 4

I am still getting some errors like when I try to bring up the attach screen. I can't even get into the error log to be able to get the error I am getting from the add button and attach screen. I get to the attach screen but can't do anything.

Thanks

Quinn

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

can you try to post the screenshot of the error?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 6

You are right about detaching the default. I changed the db name on the connection screen to get in.

Here is the message:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Failed to connect to server GISSERVER. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.

Login failed for user 'GISSERVER\qmccarthy'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

I will see if it will let me change that for my login.

Thanks

Quinn

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 7

I can't get into the user properties screen to change my default DB.

Quinn

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 8

I guess you might have already registered the Server in your SSMS Registered Servers.

If so, then right click on the Server Name and select Properties.

Then you have 2 tabs, In the Tab2 Connection Properties, Select the Default database you want to connect.

At least this should be a quick workaround.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 9

Bru

I can't get in their either. Same message. Do you think sp_attach_db from the query window would work?

Quinn

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 10

Yes, try attaching using the Stored Procedure.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 11

That worked. I seem to have restored all of my privelges and that DB looks like it is good shape. I already changed all of the users to master as the default DB. I also have a log file on this DB that need to be shrunk from 24 gb to 2. If I do a shrink files and pick the log file and check the second shrink option and set that to 2 Gb will that work?

I really appreciate all of the help I get here.

Thanks

Quinn

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

If you won't need the space, yes, use shrink files, pick the log file.

Be sure you leave space in there for normal operation.