如何重置 Sql Server 2008 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4085494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reset Sql Server 2008 database?
提问by ward87
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 数据库，就像它第一次创建时一样。必须重置所有身份。数据库中的所有数据都必须消失。有没有办法做到这一点？
采纳答案by AdaTheDev
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.
另外，我建议您保留数据库的完整创建脚本。这将提供另一种选择 - 删除数据库，并从这些脚本重新创建。
回答by Robert Koritnik
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:
我建议你在开发你的应用程序时（如果这是你在 stackoverflow 上提出问题后正在做的事情）也可以版本控制 DB 脚本。我通常定义这些数据库脚本：
- Drop 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 batfile that calls sqlcmdcommands. You can easily run batch files from within Visual Studio.
以相同的顺序一一运行，我也可以随时使用测试数据重新创建我的数据库。当我需要部署我的数据库时，我只运行 1-5 个脚本，然后将 6 个排除在外。您也可以通过创建一个bat调用sqlcmd命令的文件来自动执行此操作。您可以轻松地从 Visual Studio 中运行批处理文件。
回答by Mark Broadbent
回答by bendytree
AdaTheDev's linkdidn'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 ONstatement 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'
