Clean up your testing databases
Came across an interesting CodeProject article: a .NET application that takes a database connection string and deletes all information from all tables except those specifically marked with IsStatic attribute. Take a look at
Database Resetter.
The application is not finished yet, and crash on a table's self-reference is pretty serious… but I like the concept. The ability to easily wipe out your test DB and start over again with an empty DB is really valuable at times. Unfortunately for some, it only works on SQL Server.
October 5, 2004
В· 
Jouni Heikniemi В· 
2 Comments
Posted in: Misc. programming
2 Responses
Antti-Juhani Kaijanaho - October 6, 2004
You mean DROP DATABASE doesn't work in the .NET wonderland?
A good tester, of course, has a script that recreates an initial database, so that after DROP DATABASE you need just to run the script and you are all set for a new test run. Such a script is not merely a testing tool: it should also be used at deployment time to set up the initial production database. (Unfortunately, upgrades don't work as easily as this.)
Jouni - October 6, 2004
Of course DROP DATABASE works, and SQL Server can also generate scripts that wipe the DB tables and then recreate them, all this without dropping the DB and messing its backup jobs and other stuff. Wiping the tables and recreating their contents are not the same thing, even if they can often substitute for one another. I'd compare them to formatting and deleting all files on a hard drive partition; while both have close to similar effects, they still are very different operations by nature.
The chief problem in the recreation script is that the application wiping the database needs to have the script handy (and have the permission to execute it, which by itself is nontrivial in certain environments). If you want to automate DB wipeout before executing your daily automated unit tests for example, a normal deletion of all table content is way less drastic than what recreating the DB from the scratch would be.
Also, in order for the scripting approach to work, you will have to script all your default data to be inserted once the tables are created anew. Depending on your development model that may or may not be an issue. If the script works in your enviroment, that's good; for many other practical situations a simple DB cleanup is much easier to maintain.
For the reference: I've used drop/script-based cleanup for years. I've also used semi-automated deletion, but never bothered to push the concept of investigating the system tables as far as that application does.
Leave a Reply