Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to recreate database.

Is there any way to keep the database and just reset it?

share|improve this question
Yes, probably off-topic, but not sure, because this question is useful to every programmer when working on development databases. – Steve Feb 3 at 8:31
:) yes it looks off topic but it is handy for every project I work on. I usually test my apps with test data, and after finishing the app I need to reset it. – Mahdi Tahsildari Feb 3 at 8:33
1  
This is on topic, for all those developers who don't get their database right first time. So if you close it, you have massaged the truth. :) – Tony Hopkinson Feb 3 at 10:45
Thanks Tony ; ) – Mahdi Tahsildari Feb 3 at 11:55
In our environment we always keep a baseline db with barebones lookups populated, every deployment we do starts with that baseline db, and all schema changes are synced to it. We also have the scripts to recreate this db and the lookups in source control, for versioning. – Kevin Feb 8 at 2:05

migrated from stackoverflow.com Feb 4 at 6:59

2 Answers

up vote 1 down vote accepted

Recreate is the better option. Because.

You end up with a script to create the db

It's dirt simple.

You don't have to take the complexity of your schema in to account.

Sql server will be more than happy to create the script for you.

The other options is to write something that looks through the schema

Identifies Tables with foreign key contraints so they can be dropped

Drops them

Then find the identities and reseeds them

Then puts the FKs back

Or if you are a masochist, you could write (well try to) something to use the dependency order

Both of options 2 and 3 are high maintenance.

share|improve this answer
up vote 1 down vote

Probably this LINK will help you

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
share|improve this answer
3  
This will only work if all foreign keys are removed before that. – a_horse_with_no_name Feb 3 at 8:57
Removing foreign keys will work, so will either starting at the child end of each branch of parent-child or grandparent-parent-child relationship. Or run the above multiple time until there is no data left. This approximates the second option I listed above – Karl Feb 3 at 9:23
@Karl: "working up" the dependencies does not work. TRUNCATE is not supported on tables having an incoming foreign key. Even if there are no rows referencing the table – a_horse_with_no_name Feb 3 at 10:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.