Galin Iliev's blog

Software Architecture & Development

Empty Database for Production

It is common to test data-driven applications with dummy data to test if everythign works fine. Once it is done the easiest way to start using in production is to recreate database. In case you have real lookup data in some of the tables (as predefined user, user groups, security rights and etc) you have to script them too.

There is another way though: just clear dummy data. As we know it can be done with T-SQL DELETE statement. Unfortunately this doesnt reset autonumber fields. So if you have Autonumber columns in your tables you have two choices:

  • run TRUNCATE TABLE statement - you can use this only if there are no contrains and foreign keys related with this table
  • or delete all data and reset autonumber field (back to 1) with the following statements:
       1:  DELETE FROM tblName
       2:  DBCC CHECKIDENT (tblName, RESEED, 0)

Hope this helps