How to reset Sql Server 2008 database?

Question

I want to reset my sql server 2008 database like when it's first created. all the identities must be reset. All the data in the database must be gone. Is there a way to do this?

Answer 1

Reseed

Use these pair of statements on all data tables. Don't forget to leave lookup tables alone (like types of some sort). They should probably stay populated because other data tables rely on their values.

truncate table table_name;
dbcc checkident (table_name, reseed, 0); /* next ID will be 1 */

Development suggestion

I suggest while you develop your app (if that's what you're doing since your asking a question on stackoverflow) to also version control DB scripts. I usually define these DB scripts:

1. Drop DB
2. Create model
3. Create functions
4. Create stored procedures
5. Create static data (lookup tables data)
6. Create test data

Running one by one in the same order I can always recreate my DB with test data as well. And when I need to deploy my DB I just run scripts from 1-5 and leave 6 out of it. You can as well automate this by creating a bat file that calls sqlcmd commands. You can easily run batch files from within Visual Studio.

Answer 2

You can write a script to delete all the data from the tables and reset identity values (using DBCC CHECKIDENT). There's a number of scripts out there to do this, so I won't reinvent the wheel - here's one example.

Once you have a clean database, I'd suggest backing it up - then each time you want to reset to clean again, you can just restore from the backup.

Also, I'd recommend you keep full creation scripts for your database. That would then give another option - drop the database, and create afresh from those scripts.