October 29, 2009 at 9:34 am
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
October 29, 2009 at 9:40 am
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.
October 29, 2009 at 9:48 am
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
October 29, 2009 at 10:09 am
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.
October 29, 2009 at 10:12 am
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
October 29, 2009 at 10:15 am
can you try to post the screenshot of the error?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 10:23 am
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
October 29, 2009 at 10:26 am
I can't get into the user properties screen to change my default DB.
Quinn
October 29, 2009 at 10:30 am
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 10:41 am
Bru
I can't get in their either. Same message. Do you think sp_attach_db from the query window would work?
Quinn
October 29, 2009 at 10:46 am
Yes, try attaching using the Stored Procedure.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 11:53 am
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
October 29, 2009 at 12:11 pm
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply