Trying to restore via t-sql script. Exclusive access could not be obtained because the database is in use

I'm trying to restore backups of our production databases to our development server. When I run the following script which has previously wor开发者_开发知识库ked:

RESTORE DATABASE M2MDATA01 FROM DISK = 'C:\Install\SQLBackup\M2MDATA01.SQLBackup' WITH REPLACE,
   MOVE 'M2MDATA01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2MData01.mdf',
   MOVE 'M2MDATA01_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2MData01.ldf'

I get the following error:

    Error   12/21/2009 9:06:09 AM   0:00:00.000 SQL Server Database Error: Exclusive access could not be obtained because the database is in use.   5   0

However, I have no idea how what could possibly be using it. How can I tell?

Check what's connected (the easy way)

SELECT * FROM sys.sysprocesses S WHERE S.dbid = DB_ID('M2MDATA01')

Note: sysprocesses can not be emulated using dmvs...

Edit, check for locks too

SELECT * FROM sys.dm_tran_locks L WHERE L.resource_type = 'DATABASE' AND L.resource_database_id = DB_ID('M2MDATA01')

In SQL Server Management Studio, go to Management => Activity Monitor. This will show you all processes that are connected to all databases, and allow you to kill these processes (only recommended as a last resort).

Profiler is one option.

From Sql Server Management Studio, select Tools|Sql Server Profiler.

1. Connect to the server instance that the database you are working with is on.
2. Switch to the Event Selection tab
3. check the checkbox below the grid labeled "Show all columns"
4. In the grid find the DatabaseName column and check the entire column.
5. (optional) press the Column Filters button and filter to the database name you are working with.

This should at least tell you if something is using the database in question.

Easiest solution here is to delete the database and select the "close existing connections" checkbox before hitting okay. Then the restore will work just fine. It's just DEV right? :}

If you're restoring a db, do you really care who is connected? Or what is being done with those connections? I would think not. Just "kick everyone out" by setting the database into single user mode and then restore the database.

USER master
GO

ALTER DATABASE M2MDATA01 
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE M2MDATA01 
FROM DISK = 'C:\Install\SQLBackup\M2MDATA01.SQLBackup' 
WITH REPLACE,
MOVE 'M2MDATA01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2MData01.mdf',
MOVE 'M2MDATA01_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2MData01.ldf'
GO

Now, one additional item to be aware of. After you set the db into single user mode, someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. It's a race! My suggestion is to run all three statements at once in a single batch.