DROP Database - How??

Question

My scenario is I want to use the .sql file generated by DataDude to initialize a database for unit testing.  As part of my unit test init code I want to recreate a database from scratch and populate it with some sample data.  I have the inserts for all my sample data in the post-deployment script and that works great.  But I don't want to just do an update deployment, because I also want to wipe out any data in the database.  Rather than trying to write TRUNCATE statements for every table (and dealing with getting the order just right to deal with dependencies), I want to just DROP the database and re-create it each time.

No problem I thought, I'll just use the Create build option, and it will create the database for me.  However, the script it generates doesn't check to see if it exists already and do the DROP.  That would be a nice option, but I figured it wouldn't be an issue since I could just use the pre-deployment script and do it myself.

This doesn't appear to work as I had hoped however.  Sql in the pre-deployment script doesn't execute until AFTER the CREATE DATABASE statement.  Is there a way to get this to work without manually modifying the .sql file after the fact? 

Ideally I would like the create script to check to see if the database exists first and if so drop it.  I would also like the option to write some sql that gets executed prior to the CREATE DATABASE script.  This could be done either by making the pre-deployment script execute at the very start, or by giving me an extra pre-creation script that I can use.

Answer 1

Hi,

Here is one way you might be able to do it -

On your project properties, on the build page, uncheck the box for 'Generate script for CREATE DATABASE' statements for the 'New' build configuration. 

In your PreDeployment.sql script, put this code in at the top of the file:

USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$(databasename)')

DROP DATABASE $(databasename)

CREATE DATABASE $(databasename) COLLATE SQL_Latin1_General_CP1_CI_AS

GO

USE $(databasename)

GO

EXEC sp_dbcmptlevel N'$(databasename)', 80 

A little bit of a hack, but it might work for what you are doing.  When you deploy, it should replace $(databasename) with the name of your database as set in the Project Properties. 

 

Answer 2

Ahh excellent idea.  That should work just fine.  Thanks Tom.

In a somewhat related issue, I'm trying to figure out a good way to avoid the problem where the DROP fails because the database is in use.  I posted this question in the T-SQL forum, but if anybody reading this happens to know this is what I'm trying to do:

when I do DROP DATABASE I'm getting errors because it's in use.  But if I do a delete from the mgmt studio gui I have the option to close all connections as part of the delete.  Is there a way to achieve the same effect from a sql script?