删除数据库中所有内容并从头开始重建的最快方法？

I am designing a standard ASP.Net site with a SQL database. I have a database schema and During the tests I am changing data types amongst other tasks and the data contained inside really is not that important.

我正在设计一个带有SQL数据库的标准ASP.Net站点。我有一个数据库模式,在测试期间,我正在更改其他任务中的数据类型,并且内部包含的数据确实不那么重要。

I keep getting errors as the old data does not match the new rules. This is not important and I am happy to clear everything but currently, I have to export/publish the database to a .sql file then import it from scratch - which is time consuming.

由于旧数据与新规则不匹配,我不断收到错误。这并不重要,我很乐意清除所有内容,但是目前,我必须将数据库导出/发布到.sql文件,然后从头开始导入 - 这非常耗时。

Is there a quick button / feature that I have missed that allows you to reset autonumbers / IDs to 1 and delete all content, or just speed up what I currently do?

是否有一个我错过的快捷按钮/功能,允许您将自动编号/ ID重置为1并删除所有内容,或者只是加快我目前的工作?

6 个解决方案

#1

There are a few options you could take, the "fastest" really depends on your database.

您可以选择几个选项,“最快”取决于您的数据库。

To firstly answer your questions on seeding, etc - TRUNCATE TABLE will delete all information in a table (very fast, as it is not logged) and will reset your identity column.

首先回答关于种子等的问题 - TRUNCATE TABLE将删除表中的所有信息(非常快,因为它没有记录)并将重置您的标识列。

eg:

TRUNCATE TABLE dbo.table

http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

The significant restriction here is that you cannot use it on a table that is referenced by another table. In this case you can use a standard delete and then use DBCC CHECKIDENT

这里的重要限制是您不能在另一个表引用的表上使用它。在这种情况下,您可以使用标准删除,然后使用DBCC CHECKIDENT

eg:

DELETE FROM dbo.table
GO
DBCC CHECKIDENT(dbo.table, reseed, 0)

http://msdn.microsoft.com/en-us/library/ms176057.aspx

Remember with delete to make sure you delete information in the correct order (i.e. taking into account foreign keys).

请记住删除以确保以正确的顺序删除信息(即考虑外键)。

Another approach I often use is simply writing a complete tear-down / rebuild script when I want to reset the database. The basic premise is to tear down, or drop all database objects at the beginning of the script and then recreate them. This is not necessarily a solution for all scenarios, but for basic tasks works well for me. To avoid errors I would usually add my drop statements in IF statements, eg:

我经常使用的另一种方法是在我想重置数据库时编写完整的拆卸/重建脚本。基本前提是在脚本开头拆除或删除所有数据库对象,然后重新创建它们。这不一定是所有场景的解决方案,但基本任务对我来说效果很好。为了避免错误,我通常会在IF语句中添加我的drop语句,例如:

IF EXISTS 
(
     SELECT * 
     FROM information_schema.tables 
     WHERE table_name = 'table' AND table_schema = 'dbo'
)
BEGIN
    DROP TABLE dbo.table
END

#2

Why don't you write some T-SQL code to delete (or truncate, even quicker) all your tables? Be careful to take into consideration your integrity rules while clearing the tables: allways clean the tables containing the foreign key before cleaning the one containing the primary key.

为什么不编写一些T-SQL代码来删除(或截断,甚至更快)所有表?清除表时要小心考虑完整性规则:在清除包含主键的表之前,请彻底清除包含外键的表。

#3

If you just need to clear out data then just write a script to truncate all the data in each table. The truncate command also resets any IDENTITY fields as well.

如果您只需要清除数据,那么只需编写一个脚本来截断每个表中的所有数据。 truncate命令也会重置任何IDENTITY字段。

TRUNCATE TABLE myTable

For each table you have. Then just run that script each time.

对于您拥有的每张桌子。然后每次都运行该脚本。

#4

Here'a a quick way to delete all of the data in a table:

这是删除表中所有数据的快捷方法:

TRUNCATE TABLE YourTableName

You could write a script that would truncate all of your tables.

您可以编写一个截断所有表的脚本。

The alternative is to just DROP the table and re-create it.

另一种方法是只删除表并重新创建它。

If you really want to drop all data, then you could detach the database and create a brand new one; it's a bit extreme, but possibly faster than dropping everything first.

如果你真的想删除所有数据,那么你可以分离数据库并创建一个全新的数据库;它有点极端,但可能比首先丢弃所有东西更快。

#5

As others have suggested I find it preferable to maintain a script that builds the database from scratch and can tear down the database prior to rebuilding it. Develop this script just as you'd develop the rest of the application. I find it easier to understand the database through a script than by building it through a GUI, especially where there are complex relationships, triggers and so on.

正如其他人所建议的那样,我发现维护一个从头开始构建数据库的脚本更可取,并且可以在重建之前拆除数据库。像开发应用程序的其余部分一样开发此脚本。我发现通过脚本比通过GUI构建数据库更容易理解数据库,特别是在存在复杂关系,触发器等的情况下。

It's also useful if you have other developers, and perhaps quicker and less prone to errors than copying your working database and handing it to another developer.

如果您有其他开发人员,并且比复制工作数据库并将其交给其他开发人员,可能更快,更不容易出错,这也很有用。

On release you can freeze that script and then create delta scripts for the next release which has just the changes from the initial schema to the new. This could also tear down the new objects created in the delta before recreating them so it can be easily re-run without having to wipe the entire database.

在发布时,您可以冻结该脚本,然后为下一个版本创建增量脚本,该脚本只包含从初始模式到新模式的更改。这还可以在重新创建之前拆除在增量中创建的新对象,以便可以轻松地重新运行,而无需擦除整个数据库。

#6

if you use Visual Studio 2010 then

如果您使用Visual Studio 2010

open the App_Data folder of the solution and double click on the MDF File. right click on your table , in the menu select "Show Table Data". select all rows and delete all them.

打开解决方案的App_Data文件夹,然后双击MDF文件。右键单击表格,在菜单中选择“显示表格数据”。选择所有行并删除所有行。

#1