How Do You Check if a Database Exists…

Download PDF

How do you check if a database exists, drop it and recreate it for testing purposes?

After running the Database Corruption Challenge for several weeks now, I have seen many scripts to drop and recreate a database. This makes it clear that it takes many tries to get the corruption challenge right.

They usually start out with something like the following:

  • If the database exists
    • Kick everyone out (several different ways of doing this)
    • Drop the database
  • Restore the test database or create the database depending on the scenario
  • Do stuff…

What has really surprised me is the number of different ways that people perform the first two steps above, checking to see if the database exists, and then kicking everyone out.

The way I usually do it is something like this….

IF EXISTS(SELECT name
            FROM sys.databases
	   WHERE name = 'CorruptionChallenge1')
BEGIN
	ALTER DATABASE [CorruptionChallenge1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [CorruptionChallenge1];
END

However I have seen many other ways to do something similar. I am wondering for those who do something different, how do you do it, and why? I don’t mean that in a bad way, I am curious to understand the benefits of each way of doing it.

Please post your response and comment to why, or comment on an existing method to drop and get started with a clean database.

Tagged with: , ,
3 comments on “How Do You Check if a Database Exists…
  1. Patrick Flynn says:

    There is a blog post from Greg Low that discusses some of the issues in reliably dropping a database:
    http://sqlblog.com/blogs/greg_low/archive/2014/01/06/reliably-dropping-a-database-in-a-t-sql-script-is-too-hard.aspx.

    It appears that the only fully effective method to drop a database is to
    (a) Kill all connections
    (b) Place it into an offline or other state that prevents connections from other users
    (c) Drop database
    (d) If necessary, clean-up any files that may be left behind

    The variations of :
    Switch to master
    If DB exists Then
    Kill Users
    Drop Database

    all suffer from the problem discussed.

  2. Rob Farley says:

    You’ll notice he mentions me. I figure that if a system is going to suffer from connection requests preventing the drop, then scripting something to remove the files of an offline database isn’t too hard.

  3. HanShi says:

    This is my approach:

    IF EXISTS(SELECT name
    FROM sys.databases
    WHERE name = ‘CorruptionChallenge1’)
    BEGIN
    ALTER DATABASE [CorruptionChallenge1] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    END
    –restore database from backup
    ….

    Because the database is set OFFLINE you’ll know for sure no connections can be made. In the past I had issues after setting the database to SINGLE_USER when some polling application is getting the single connection and blocking me from connecting.
    Deleting an OFFLINE database doesn’t delete the files from disk, but I always skip this part. After setting the database OFFLINE I don’t delete the database, but immediately start the restore. This will use the existing file allocation on disk and saves time (no zero-out of the allocation on disk).

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.