Click here to Skip to main content
13,252,259 members (58,022 online)
Click here to Skip to main content
Add your own
alternative version


35 bookmarked
Posted 3 Oct 2004

Database Resetter

, 5 Oct 2004
Rate this:
Please Sign up or sign in to vote.
A little tool to make life while developing with databases a little easier.

Sample Image - Database_Resetter.gif


This article describes a small utility that can be used to reset a database to a clean state. During development of applications that use databases, this may be needed quite frequently.

This utility was built quickly and will therefore not win any style points, but it works for me!


The idea for this utility was born out of pure frustration. In a number of projects, we were working with a database in which both the structure and the default data changed very often. The changes were mostly minor, but because of these small changes, it became increasingly difficult to keep up an efficient work process. Changes in both structure and default data had to be made in two places. A bulk import of either test data or actual data had to be done as well. All of this opened the door for small (and bigger) mistakes.

This utility - that should have been written over a year ago - relies on certain characteristics of a database to remove all non-default data from it and clean up the autonumber (or identity) values so that new data can be inserted nicely without nasty gaps in IDs.

Using the code

The code that does all the work is contained in a separate library, so it can be reused by different projects. There is a standalone application which can be used to reset a database in a matter of seconds. The library could also be included in an import tool enabling that tool to reset the database before importing the data.

The basic idea behind this utility is to mark all default data as such, and delete anything that is not marked. To mark the default data, a specific column is added to those tables containing default data. In the example project, this column is called IsStatic. It is of type bit and should have the value of 1 if the record should be retained. Any record where IsStatic is set to 0 will be deleted. Any table that doesn't contain an IsStatic column will be emptied in its entirety.

The library can be used in two ways. It can be used to bulk-erase the database, or it can be used to erase the database on a per-table basis. The latter allows for feedback on the progress.

To use the code, simply instantiate the class, supply it a connection string and the name of the column to test for. Using the ResetAllTables function results in a bulk-erase of the entire database.

Dim resetter As PhoenixConsultancy.Utilities.Database.Reset = _
    New PhoenixConsultancy.Utilities.Database.Reset

resetter.StaticColumnName = "IsStatic"
resetter.ConnectionString = connectionString

TextBoxResults.Text &= "Bulk reset completed." & vbCrLf

In order to reset the tables one by one, a list of tables can be received and looped over. The list of tables also contains information on the number of records deleted and whether or not the table has been reset. This list can also be obtained after a bulk-reset in order to supply status information.

Dim resetter As PhoenixConsultancy.Utilities.Database.Reset = _
    New PhoenixConsultancy.Utilities.Database.Reset
Dim tables As PhoenixConsultancy.Utilities.Database.TableInfo()
Dim table As PhoenixConsultancy.Utilities.Database.TableInfo

resetter.StaticColumnName = "IsStatic"
resetter.ConnectionString = connectionString

tables = resetter.Tables
For Each table In tables
    If table.ResetDone Then
    TextBoxResults.Text &= "Table " & table.Name & _
        " was already reset, " & table.RecordsDeleted & _
        " records were deleted" & vbCrLf
    If table.ResetDone Then
    TextBoxResults.Text &= "Table " & table.Name & _
        " has been reset, " & table.RecordsDeleted & _
        " records were deleted" & vbCrLf
    TextBoxResults.Text &= "Table " & table.Name & _
        " has *not* been reset, " & table.RecordsDeleted & _
        " records were deleted" & vbCrLf
    End If
    End If

The downloadable zip file contains both source and binaries for the library and a demo application. Be careful when using this utility. Make a backup of your database before testing it! Review the source if you're not sure what this utility does!

Points of Interest

The two main issues that needed to be solved were the inter-table dependencies and resetting the identity values.

To solve the dependency issue, I made use of the system tables in SQL Server. Using these tables, I can (quite simply, once you know how) find out which table relies on which other table. Using a recursive function, I traverse through the tree of dependencies and reset the tables without dependencies first. After a table has been reset, a flag is set so the same table will not be reset again. The query used to find related tables looks like this:

SELECT AS RelatedTable
FROM    sysobjects
INNER JOIN  syscolumns ON =
INNER JOIN  sysforeignkeys ON = sysforeignkeys.fkeyid
   AND  syscolumns.colid = sysforeignkeys.fkey
INNER JOIN  syscolumns syscolumns2 
   ON sysforeignkeys.rkeyid =
   AND  sysforeignkeys.rkey = syscolumns2.colid
INNER JOIN  sysobjects sysobjects2 ON =
WHERE = 'TableName'

Resetting the identity values is (again, once you know how) not difficult at all. According to Microsoft's documentation, there are two ways of doing this. I chose the way in which SQL Server figures it out for itself. First, I reset the identity to the lowest possible value. This will result in errors if the table contains data and you try an insert.


To straighten things out again, I issue the same query with one less parameter. Now SQL Server figures out what the highest ID is that's currently in use and adjusts the identity value accordingly. The result is a table in which new inserts are nicely in order.



I found and fixed a minor problem with resetting the identities. When setting the new seed to 1 (as I did at first) any new insert will begin at ID 2. This is annoying when the table is completely empty. It might even break code that relies on ID 1 to exist.

Apart from the above there isn't much history yet. There is some work to do though. Some of the features I'd like to add are:

  • An IsStatic column remover for production / deployment databases
  • An IsStatic column addition tool to make it easier to use the utility on databases that haven't been prepared yet.

These features are not difficult to implement, but time is a precious good...


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Michiel de Rond
Web Developer
Netherlands Netherlands
Michiel de Rond has been programming since he was about 10 years old. In the old days he developed DOS and Windows based applications. Later the focus has shifted towards Web development. Nowadays Client/Server applications employing current technology are his thing. Michiel is based in Amsterdam and works as an independent consultant/architect/developer. Next to his development work he also writes technical books (in dutch for now). In his precious spare time he loves to spend time with his wife and daughter.

You may also be interested in...

Comments and Discussions

GeneralAll my diagrams are away :-( Pin
jsp_codeproject24-Aug-05 2:38
memberjsp_codeproject24-Aug-05 2:38 
GeneralRe: All my diagrams are away :-( Pin
Michiel de Rond24-Aug-05 9:58
memberMichiel de Rond24-Aug-05 9:58 
QuestionDeleting LnkServers? Pin
johnu13-Oct-04 13:01
memberjohnu13-Oct-04 13:01 
AnswerRe: Deleting LnkServers? Pin
Michiel de Rond13-Oct-04 19:54
memberMichiel de Rond13-Oct-04 19:54 

Thanks for your positive feedback!

I'm not sure about the linkservers, but if it's just a call to a stored procedure I can tell you how to do that. I'm not near my code and examples right now (having breakfast at home Smile | :) ), so I'll send you an example in about an hour.
Questionbackup and restore? Pin
Håkan Nilsson8-Oct-04 5:48
memberHåkan Nilsson8-Oct-04 5:48 
AnswerRe: backup and restore? Pin
Michiel de Rond8-Oct-04 5:59
memberMichiel de Rond8-Oct-04 5:59 
Generalbackup and restore Pin
Håkan Nilsson8-Oct-04 5:43
memberHåkan Nilsson8-Oct-04 5:43 
GeneralRe: backup and restore Pin
Michiel de Rond8-Oct-04 5:50
memberMichiel de Rond8-Oct-04 5:50 
GeneralRe: backup and restore Pin
ranju. v10-Oct-04 2:06
memberranju. v10-Oct-04 2:06 
GeneralRe: backup and restore Pin
Michiel de Rond10-Oct-04 5:32
memberMichiel de Rond10-Oct-04 5:32 
GeneralRe: backup and restore Pin
Håkan Nilsson10-Oct-04 22:07
memberHåkan Nilsson10-Oct-04 22:07 
GeneralRe: backup and restore Pin
Rami Saad8-Jan-05 16:25
memberRami Saad8-Jan-05 16:25 
GeneralRe: backup and restore Pin
Håkan Nilsson10-Oct-04 21:50
memberHåkan Nilsson10-Oct-04 21:50 
GeneralRe: backup and restore Pin
Rami Saad8-Jan-05 16:24
memberRami Saad8-Jan-05 16:24 
QuestionUsage of TRUNCATE TABLE instead of DELETE? Pin
Dennis C. Dietrich6-Oct-04 4:50
memberDennis C. Dietrich6-Oct-04 4:50 
AnswerRe: Usage of TRUNCATE TABLE instead of DELETE? Pin
Michiel de Rond6-Oct-04 5:07
memberMichiel de Rond6-Oct-04 5:07 
GeneralRe: Usage of TRUNCATE TABLE instead of DELETE? Pin
Dennis C. Dietrich6-Oct-04 5:58
memberDennis C. Dietrich6-Oct-04 5:58 
GeneralRe: Usage of TRUNCATE TABLE instead of DELETE? Pin
Michiel de Rond6-Oct-04 10:11
memberMichiel de Rond6-Oct-04 10:11 
AnswerRe: Usage of TRUNCATE TABLE instead of DELETE? Pin
Anthony Kirwan7-Oct-04 22:10
memberAnthony Kirwan7-Oct-04 22:10 
GeneralRe: Usage of TRUNCATE TABLE instead of DELETE? Pin
Michiel de Rond8-Oct-04 0:06
memberMichiel de Rond8-Oct-04 0:06 
GeneralSelf-referring tables Pin
Jouni Heikniemi5-Oct-04 7:56
memberJouni Heikniemi5-Oct-04 7:56 
GeneralRe: Self-referring tables Pin
Michiel de Rond5-Oct-04 8:56
memberMichiel de Rond5-Oct-04 8:56 
GeneralIsStatic column Pin
chaldon4-Oct-04 23:24
memberchaldon4-Oct-04 23:24 
GeneralRe: IsStatic column Pin
Michiel de Rond5-Oct-04 0:16
memberMichiel de Rond5-Oct-04 0:16 
GeneralRe: IsStatic column Pin
Jouni Heikniemi5-Oct-04 8:01
memberJouni Heikniemi5-Oct-04 8:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 5 Oct 2004
Article Copyright 2004 by Michiel de Rond
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid