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?
我想重置我的sql server 2008数据库，就像它第一次创建时一样。必须重置所有身份。数据库中的所有数据都必须消失。有没有办法做到这一点？
4 个解决方案
#1
6
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.
您可以编写脚本来删除表中的所有数据并重置标识值（使用DBCC CHECKIDENT）。有很多脚本可以做到这一点，所以我不会重新发明* - 这是一个例子。
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.
另外，我建议您为数据库保留完整的创建脚本。然后，这将提供另一个选项 - 删除数据库，并从这些脚本重新创建。
#2
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 *) to also version control DB scripts. I usually define these DB scripts:
我建议你开发你的应用程序（如果这是你在*上提出问题时所做的那样）以及版本控制数据库脚本。我通常定义这些DB脚本：
- Drop DB
- 丢弃DB
- Create model
- 创建模型
- Create functions
- 创建功能
- Create stored procedures
- 创建存储过程
- Create static data (lookup tables data)
- 创建静态数据（查找表数据）
- 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.
以相同的顺序逐个运行我总是可以使用测试数据重新创建我的数据库。当我需要部署我的数据库时，我只需从1-5运行脚本，然后从中删除6。您也可以通过创建调用sqlcmd命令的bat文件来自动执行此操作。您可以从Visual Studio中轻松运行批处理文件。
#3
1
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).
否则最简单的方法是恢复你应该在开始时采取的备份（adathedev的反应很好）。
#4
1
AdaTheDev's link didn't quite work for me, but it did put me on the right path.
AdaTheDev的链接并不适合我，但确实让我走上了正确的道路。
Here's what ended up working for me. I had to add the SET QUOTED_IDENTIFIER ON statement before the delete.
这就是最终为我工作的东西。我必须在删除之前添加SET QUOTED_IDENTIFIER ON语句。
/*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'
#1
6
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.
您可以编写脚本来删除表中的所有数据并重置标识值（使用DBCC CHECKIDENT）。有很多脚本可以做到这一点，所以我不会重新发明* - 这是一个例子。
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.
另外，我建议您为数据库保留完整的创建脚本。然后，这将提供另一个选项 - 删除数据库，并从这些脚本重新创建。
#2
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 *) to also version control DB scripts. I usually define these DB scripts:
我建议你开发你的应用程序（如果这是你在*上提出问题时所做的那样）以及版本控制数据库脚本。我通常定义这些DB脚本：
- Drop DB
- 丢弃DB
- Create model
- 创建模型
- Create functions
- 创建功能
- Create stored procedures
- 创建存储过程
- Create static data (lookup tables data)
- 创建静态数据（查找表数据）
- 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.
以相同的顺序逐个运行我总是可以使用测试数据重新创建我的数据库。当我需要部署我的数据库时，我只需从1-5运行脚本，然后从中删除6。您也可以通过创建调用sqlcmd命令的bat文件来自动执行此操作。您可以从Visual Studio中轻松运行批处理文件。
#3
1
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).
否则最简单的方法是恢复你应该在开始时采取的备份（adathedev的反应很好）。
#4
1
AdaTheDev's link didn't quite work for me, but it did put me on the right path.
AdaTheDev的链接并不适合我，但确实让我走上了正确的道路。
Here's what ended up working for me. I had to add the SET QUOTED_IDENTIFIER ON statement before the delete.
这就是最终为我工作的东西。我必须在删除之前添加SET QUOTED_IDENTIFIER ON语句。
/*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'