Restore failed for Server '%'. %Exclusive access could not be obtained because the database is in use.%

This article contains steps that pose risk, and should only be implemented by those with an understanding of the technologies and the risks involved.   

This article is specific to Microsoft Dynamics GP users, but contains some information, which is likely relevant to other SQL users in general.  You are here because you may have encountered the following error - or something like it:

When this error occurs, click OK, then Script the restore operation to SQL Query Analyzer, so you can run it after you have addressed the problems with Exclusive access.  Your query should look something like this.

RESTORE DATABASE [DEMO2] 
FROM  DISK = N'C:\TechNotes\2013YEU\TWO_BackUP_09142014.bak' 
WITH  FILE = 1,  
MOVE N'GPSTWODat.mdf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Dat.mdf',  
MOVE N'GPSTWOLog.ldf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

GO

If you searched for this specific error, you are likely fairly certain you don't have anyone actually accessing the database - BUT:

First - do your due diligence to ensure you have constructive Exclusive access to the database.  

Are users accessing the database?

These are both useful queries to determine what users, if any are accessing the database - both can be run from the master database: 

Select * from Dynamics..Activity -- Users in Dynamics GP

sp_who2 database -- Users accessing SQL Databases via any means

Users who are accessing the database using Great Plains should appear in the Activity Table in the Dynamics database.  

Contact them and make sure they are out.  Reach out and remote into their desktop, or assume control of their Remote Session.   Just make sure you're not terminating the session of a user who is actually using GP - this can have serious consequences.  

When you are sure they are out, you likely have a stuck users.  Delete these record(s) from the Activity database.

delete from Dynamics..Activity -- This query will delete ALL records from the Activity Table

Other users should be visible when using the sp_who2 query, which can be run in any database, and will show detailed information about users' status. 

Specifically, what databases are displayed in the DBName column.

You can see above, the problem  is a sleeping session of the sa user in the DEMO2 database.  

To end the session, you must Kill it, risk, risk, risk...  Open Query Analyzer and execute the query below, where 57 is the SQL Process ID occupied by the sleeping user process - processes should not be terminated lightly, make sure you have correctly identified the problem user.

Kill 57

Since you are experiencing problems obtaining Exclusive Access to the database, I suggest not using the point and click interface of SQL Server Management Studio to do the restore.  If fact, adding a couple of lines to your database restore script, captured above, can alleviate any additional problems you might encounter.

USE master
Alter database DEMO2 set single_user with rollback immediate
RESTORE DATABASE [DEMO2] 
FROM  DISK = N'C:\TechNotes\2013YEU\TWO_BackUP_09142014.bak' 
WITH  FILE = 1,  
MOVE N'GPSTWODat.mdf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Dat.mdf',  
MOVE N'GPSTWOLog.ldf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

GO

Adding the USE master statement line highlighted above, makes sure you are not accessing and therefore locking your target database.   Altering the target database, prevents other users from connecting to it.

It seems like only once in a 'blue moon' this kind of error occurs, but when it does, it can be more than moderately annoying.  

Since the introduction of SQL Change Tracking with Management Reporter, this type of locking is more common. The default refresh rate of Management Reporter, is a contributing factor and can be changed. Additionally, when restoring a database, you can stop Management Reporter Services using The Configuration Console.