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.