SQL Server 2012 – Drop and Create Script Not Working

sql serversql-server-2012ssms

I'm fairly new to databases, but I'm beating my against the wall trying to do something that (I believe) should be fairly trivial.

I'm using SQL Server Management Studio 2012 and am trying to delete all data in my database. This is not a production database, and I am the only one with access. I've got several relations between tables, so after reading some it seems like a DROP and CREATE should be the easiest route to get around the fact that I have foreign keys. So, using "Tasks -> Generate Scripts" and generating a "DROP and CREATE" script, I keep getting the following error (and the script endlessly executes):

Msg 3702, Level 16, State 4, Line 2

Cannot drop database "emcb_schema_v1_0_7" because it is currently in use.

Msg 1801, Level 16, State 3, Line 2

Database 'emcb_schema_v1_0_7' already exists. Choose a different database name.

I've made sure that I run the script from master, so what gives? In my frustration, I'm blaming Microsoft for making this such a difficult task, but it could easily be an operator error…

Best Answer

The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from master but also that none of your other query windows, Object Explorer, etc. still have a lingering connection to the database. The easiest way to do that is to set the database to SINGLE_USER:

USE [master];
GO
ALTER DATABASE [emcb_schema_v1_0_7] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [emcb_schema_v1_0_7];
Related Question