SQL Server – Drop database, drop current connections first

April 22, 2009
171 Views

Hi!

I write scripts to generate my database, I want my scripts to run from command line using SQLCMD and (at the same time) use SSMS to edit the scripts and some times run them there. This creates a problem when dropping databases. Databases will not be dropped while they have open connections.

The solution is easy; here goes:

ALTER DATABASE x SET SINGLE_USER WITH ROLLBACK IMMEDIATE

In a sample:

PRINT ‘Recreate database…’
IF EXISTS (SELECT name FROM sys.databases WHERE name=’

Some_DB’)
BEGIN
    ALTER DATABASE Some_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Some_DB
END
GO
CREATE DATABASE Some_DB
GO

Gorm Braarvig


Hi!

I write scripts to generate my database, I want my scripts to run from command line using SQLCMD and (at the same time) use SSMS to edit the scripts and some times run them there. This creates a problem when dropping databases. Databases will not be dropped while they have open connections.

The solution is easy; here goes:

ALTER DATABASE x SET SINGLE_USER WITH ROLLBACK IMMEDIATE

In a sample:

PRINT ‘Recreate database…’
IF EXISTS (SELECT name FROM sys.databases WHERE name=’

Some_DB’)
BEGIN
    ALTER DATABASE Some_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Some_DB
END
GO
CREATE DATABASE Some_DB
GO

Gorm Braarvig