Dropping and recreating databases in Microsoft SQL Server
If you are unable to drop and create database in SQL server, this code will close all active connections to the database and then drop it
WHILE EXISTS(select NULL from sys.databases where name=‘yourDBname’)
BEGIN
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,”) + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(N ‘yourDBname’) AND SPId <> @@SPId
EXEC(@SQL)
DROP DATABASE ‘yourDBname’
END
GO
CREATE DATABASE yourDBname
GO
/*********************************************************************************
OR
*********************************************************************************/
DECLARE @DBName varchar(50) = ‘yourDBname’
USE master
IF EXISTS(select * from sys.databases where name= @DBName)
EXEC(‘DROP DATABASE ‘ + @DBName)
EXEC(‘CREATE DATABASE ‘ + @DBName)
RELATED POSTS
Install Adventure Works database from backup
The Adventure Works 2014 Data Warehouse database can be installed by restoring a database backup.Read More