November 10, 2010 at 8:23 am
I just discovered that I have an instance each of SQL Server 2005 Express and SQL Server 2008 Express. I want to delete the 2005 one after moving its databases to 2008.
1. I had about ten small databases to move from 2005. I managed to detach the first nine OK, but the last one failed with a warning that there were other connections active. I used sp_who2 to view the other connections (Sleeping and Runnable) and then used {ALTER DATABASE 'WILDCATS' SET SINGLE_USER WITH ROLLBACK_IMMEDIATE}
which helped to delete the unwanted connections, but now I am unable to detach or drop the database, even when running with Admin privileges, as I keep getting the error below:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Failed to connect to server LAPTOP. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot open database "WILDCATS" requested by the login. The login failed.
Login failed for user 'Laptop\Bilal'. (Microsoft SQL Server, Error: 4060)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4060&LinkId=20476%5B/quote%5D
To see if the error applied to this db only, I created a dummy db called NEWDB, but cannot detach / drop it either.
What can I do to fix it?
2. I went to Vista | Control Panel | Programs and Features | to uninstall the 2005 instance, but all the programs listed under SQL Server there are of the 2008 and 2008 R2 editions. I want to play careful and since I can't figure which one to use, I have decided to seek advice first. Should I select 'Microsoft SQL Server 2008' (plain vanilla)?
3. When I tried attaching the successfully detached databases on the 2008 instance, I getthis error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'LAPTOP\SQLEXPRESS'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\VB.Net Stuff\VB Samples\Entity Framework Tutorials\Quickstart\School.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476%5B/quote%5D
I am at a total loss. All help is welcome.
November 10, 2010 at 8:50 am
1. Your login doesn't have rights to this database for some reason. Do you have an sa/sysadmin level login? How are you doing the detach?
2. no. There should be some install there, but if this was installed as part of something like Visual Studio, it might not say SQL Server 2005. Look for something else with 2005 in it.
3. I believe this is a permissions error. Did you move the mdf/ldf to the data folder for 2008? That service account needs rights to the files to attach them.
November 10, 2010 at 12:16 pm
Thanks, Steve.
Steve Jones - SSC Editor (11/10/2010)
1. Your login doesn't have rights to this database for some reason. Do you have an sa/sysadmin level login? How are you doing the detach?
I am detaching from Management Studio 2008, like I did all the others. First, I connect to the server using Windows authentication. I am a member of Administrators in Windows and the BUILTIN\Administrators group has sysadmin privileges in SQL Server. I just checked under Security | Logins, my login LAPTOP\BILAL has been assigned all server roles.
I also checked User Mappings for the two remaining databases, WILDCATS and NEWDB, my login has dbowner and public roles set. As I mentioned, I created the NEWDB just yesterday, so I should have all rights to these databases. I am sure the SET SINGLE USER WITH ROLLBACK_IMMEDIATE statement is the cause of my grief as this affects all databases in both instances. Any idea on how I can fix it?
2. no. There should be some install there, but if this was installed as part of something like Visual Studio, it might not say SQL Server 2005. Look for something else with 2005 in it.
I checked under Programs And Features in Control Panel, the only items with 2005 in the name are APEXCODE2005 and C++2005 REDISTRIBUTABLE. I may have inherited it when I moved from 2005 to 2008. I just want to know how to uninstall the one instance, by brute force if necessary, without uninstalling everything!
3. I believe this is a permissions error. Did you move the mdf/ldf to the data folder for 2008? That service account needs rights to the files to attach them.
Thanks for the tip. I did not know that you need to place the mdf and ldf files in a designated folder before attaching, I presumed you could store them at random! I tried your advice and yes, it worked.
November 10, 2010 at 2:53 pm
If you want to do it with brute force, for the last db, can't you do something like:
Use Master
Alter Database DB_NAME
SET SINGLE_USER With ROLLBACK IMMEDIATE
And include the backup statement?
Sorry I didn't read that you had already tried this. Please disregard.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply