Database Resetter

• Download demo project - 28 Kb

Introduction

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!

Background

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

resetter.Initialize()
resetter.ResetAllTables()
TextBoxResults.Text &= "Bulk reset completed." & vbCrLf
resetter.DeInitialize()

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

resetter.Initialize()
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
    Else
    resetter.ResetTable(table)
    If table.ResetDone Then
    TextBoxResults.Text &= "Table " & table.Name & _
        " has been reset, " & table.RecordsDeleted & _
        " records were deleted" & vbCrLf
    Else
    TextBoxResults.Text &= "Table " & table.Name & _
        " has *not* been reset, " & table.RecordsDeleted & _
        " records were deleted" & vbCrLf
    End If
    End If
Next
resetter.DeInitialize()

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  sysobjects.name AS RelatedTable
FROM    sysobjects
INNER JOIN  syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN  sysforeignkeys ON syscolumns.id = sysforeignkeys.fkeyid
   AND  syscolumns.colid = sysforeignkeys.fkey
INNER JOIN  syscolumns syscolumns2 
   ON sysforeignkeys.rkeyid = syscolumns2.id
   AND  sysforeignkeys.rkey = syscolumns2.colid
INNER JOIN  sysobjects sysobjects2 ON syscolumns2.id = sysobjects2.id
WHERE   sysobjects2.name = '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.

DBCC CHECKIDENT('TableName', RESEED, 0)

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.

DBCC CHECKIDENT('TableName', RESEED)

History

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...