Scrub all records in a SQL database...

Question

I was wondering what you would use to scrub a database of all test data (leaving the structure intact) prior to going into production?

I use something like:

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?'
DBCC CHECKIDENT (''?'', RESEED, 0)
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Which I think I picked up on the net somewhere, however, it doesn't always seem to reseed everything back to zero.

Answer 1

I normally generate the DDL statements from the dev database and then create the database from scratch on the production server.

Answer 2

We keep the scripts to build the database in version control. When we need to purge test data we drop the database and recreate it. Another option is to recover a backup that is in the state you want.

Answer 3

I would create create/drop scripts for tables you need scrubbed and just wipe them out and recreate them.

Answer 4

The common approach is to have a set-up script that drops all tables and then recreates them. This has the benefit over just wiping the data of persisting any changes too.

Answer 5

To reseed your identity columns, you can add the following line to your reset script.

EXEC sp_MSForEachTable 'DBCC CHECKIDENT('?', RESEED, 0)'

You can change the 0 in the command to be whatever your default value should be.