SQL Server: Unable to restore Database, Database is in use by session

Question

I am using SQL Server 2005 and I am having an issue restoring my database I receive this message upon trying to restore my database.

Restore failed. (Microsoft.SqlServer.Express.Smo)

"System.Data.SqlClient.SqlError: RESTORE cannot process database 'AMOD' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Express.Smo)"

I have restarted the program I did not open any tables contained within the database and I still receive this message. I am new to SQL Server and this is my first time doing a restore. I appreciate any help provided.

Answer 1

You need to kick all the users out, and make sure you're not in that database too. Assuming you're in Management Studio, you need to change your context to a different database (or switch the database dropdown to a different database) this way, and this will also kick out any other users (which could be you - Object Explorer, Object Explorer Details, other query windows, etc. could all be inadvertently preventing the restore by maintaining a connection to your database):

USE master;
GO
ALTER DATABASE AMOD SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Once you've finished restoring and the database is ready for use again:

ALTER DATABASE AMOD SET MULTI_USER;