Bulletproof way to DROP and CREATE a database under Continuous Integration.
- by H. Abraham Chavez
I am attempting to drop and recreate a database from my CI setup. But I'm finding it difficult to automate the dropping and creation of the database, which is to be expected given the complexities of the db being in use. Sometimes the process hangs, errors out with "db is currently in use" or just takes too long. I don't care if the db is in use, I want to kill it and create it again. Does some one have a straight shot method to do this? alternatively does anyone have experience dropping all objects in the db instead of dropping the db itself?
USE master
--Create a database
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'mydb')
BEGIN
ALTER DATABASE mydb
SET SINGLE_USER --or RESTRICTED_USER
--WITH ROLLBACK IMMEDIATE
DROP DATABASE uAbraham_MapSifterAuthority
END
CREATE DATABASE mydb;
