asked
SQL Server 2008 SMS won't restore
I've been using SQL Server 2005 SMS for a few years and have had no problem backing up my database and restoring to my test database (the server being accessed is 2005 Standard). I've now had the computer upgraded, and got a new version of SQL Server 2008 R2 on my machine. The SMS now comes up with this error when trying to do a restore: >**Exclusive access could not be obtained because the database is in use.** I'm the only user of the test database, and it only gets queried occasionally so it's never always being used. Any ideas on how I can do my restores now? **sp_who2:** ![spwho2][1] **Error Screenshot: (note, the server is NOT running sqlexpress, we just left the name when we upgraded** ![Restore Error][2] **EDIT:** In the end, we rebooted the server and the problem went away. Thanks for the help. [1]: /upfiles/spwho2_1.png [2]: /upfiles/restoreerror.png
Make sure you're not in the database when you try to restore it. Check the output of `sp_who` for details of who's connected. How are you attempting to restore the database?
sp_who shows me only, but I'm just in SMS, right-clicking on the database, and using the restore option, with the dialog box that follows.
and there are no records in that resultset that have your database name in the `dbname` field?
...for all connections? Or are you only seeing the one? what about the results from sp_who2? What version of SSMS are you using? Can you show the code that SSMS is generating? This may also help.
Assuming it's not a production database and it's not you connected to the database, you can do this: ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE But doing that on a production system is inherently dangerous. If legitimate users are on the system, this will blast them off.
ok, tried it, but it said it `failed because a lock could not be placed on database`.
It sounds like you're connected to the database. Do you have it open in the Object Explorer window? If you've clicked on it there, you have a connection even if you close all the query windows where you're connected.
No, I just tried completely restarting SMS, and only selected the database so I could use the right-click context menu to restore, and the same result happened.
Something is connected to the database. Oh, change the script above and try changing it to RESTRICTED_USER. See if that works.
ok, that gave me a success message, but the restores still give the same error.
Given your screenshot from the `sp_who2` has returned only one row, it seems to me as though you're not admin on that server, therefore you can't actually see anyone else connected to your database. Is this the case?
Yes, I'm not the ultimate admin though I have full permissions on the two databases I'm working on.
