SQL Server – Drop database, drop current connections first

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