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