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?


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 option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds 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.


Probably this LINK will help you

  • 3
    This will only work if all foreign keys are removed before that. – a_horse_with_no_name Feb 3 '13 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 '13 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 '13 at 10:50

Execute both queries to reset auto incremented id

string Query1 = @"ALTER TABLE [Your Table] DROP COLUMN id";
string Query2 = @"ALTER TABLE [Your Table] ADD id int IDENTITY";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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