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?

Solution 1

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.

Solution 2

If you are talking about your server then you can rebuild your system databases link text

otherwise the easiest way would be to restore the backup you should have taken at the beginning (adathedev's response is a good one).

Solution 3

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.

Solution 4

AdaTheDev's link didn't quite work for me, but it did put me on the right path.

Here's what ended up working for me. I had to add the SET QUOTED_IDENTIFIER ON statement before the delete.

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

View more solutions