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

Question

I'm trying to restore backups of our production databases to our development server. When I run the following script which has previously worked:

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?

Answer 1

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.

Answer 2

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')

Answer 3

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).

Answer 4

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? :}