Drop DB using MSBuild even if in use

Question

I am using the following command in my MSBuild file to drop a database

sqlcmd -E -S <ServerName> -Q "DROP DATABASE <DBName>"

But sometimes I get the error

Cannot drop database because it is currently in use.

What command should I use so that the DB is dropped even if it is in use?

Close all connections to the DB and then try DROP DATABASE command. — Shankar Narayana Damodaran, Jan 27 '12 at 16:51

score 19 · Answer 1 · answered Jan 27 '12 at 17:09

19

You can set your database to SINGLE_USER to drop all existing connections.

sqlcmd -E -S <ServerName> -Q "ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
sqlcmd -E -S <ServerName> -Q "DROP DATABASE <DBName>"

This is preferred to looping over each of the connections and dropping them one by one, because some applications will immediately reconnect.

answered Jan 27 '12 at 17:09

Michael Fredrickson

36,839
5
92
109

1

It also prevents new users from connecting;-) – EBarr Jan 27 '12 at 17:27
@Michael Thanks for posting the very short answer instead of linking to it. – jordanbtucker Jul 22 '14 at 20:16

score 3 · Accepted Answer · edited Apr 15 '13 at 07:46

3

To kill all connections to your database. See Kill All Active Connections To A Database

Now issue your DROP DATABASE command after the above procedure.

edited Apr 15 '13 at 07:46

NoNaMe

6,020
30
82
110

answered Jan 27 '12 at 17:02

Shankar Narayana Damodaran

68,075
43
96
126

score 0 · Answer 3 · edited Apr 15 '13 at 07:43

0

Please use something like this:

sqlcmd -S servername\instance -Q"use master;DROP DATABASE yourdatabase"

edited Apr 15 '13 at 07:43

François Févotte

19,520
4
51
74

answered Apr 15 '13 at 07:21

Paul Voicu

53
1
3

score 0 · Answer 4 · edited May 17 '13 at 17:55

0

Try this:

sqlcmd -S .\MAPS -Q "RESTORE DATABASE Awards FROM DISK = 'C:\Awards_Project
\Awards_FULL.bak' WITH REPLACE, MOVE 'Awards' to 'C:\Awards_Project\Awards.mdf',
MOVE 'Awards_log' to 'C:\Awards_Project\Awards.ldf'"

edited May 17 '13 at 17:55

cheesemacfly

11,622
11
53
72

answered May 17 '13 at 17:35

user2394932

1

score 0 · Answer 5 · answered Nov 17 '16 at 11:10

You could alternatively restart the sql service and then perform the drop e.g. in a myfile.bat

@echo off
REM Requires administrative rights to restart service
REM open powershell and execute command in administrative mode
powershell -Command "Start-Process 'cmd' -Verb RunAs -ArgumentList '/c net stop MSSQLSERVER && net start MSSQLSERVER && exit'"      
echo Please wait ~10 seconds for sql service to restart. 
Timeout /t 10 /nobreak
echo Dropping database. 
sqlcmd -S localhost -Q "drop database [database Name]"

score -1 · Answer 6 · edited Apr 15 '13 at 07:45

-1

You cannot drop a database that is in use. Check link for ref

edited Apr 15 '13 at 07:45

NoNaMe

6,020
30
82
110

answered Jan 27 '12 at 16:51

Arran

24,648
6
68
78

So, is there some way using sqlcmd that I can close all existing connection? – Achinth Gurkhi Jan 27 '12 at 16:56

Drop DB using MSBuild even if in use

6 Answers6