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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
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.
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.
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).