Quickest way to delete all content in a database and rebuild from scratch?

Question

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.

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.

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?

Answer 1

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.

Answer 2

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.

eg:

TRUNCATE TABLE dbo.table

http://msdn.microsoft.com/en-us/library/aa260621%28SQL.80%29.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

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 EXISTS 
(
     SELECT * 
     FROM information_schema.tables 
     WHERE table_name = 'table' AND table_schema = 'dbo'
)
BEGIN
    DROP TABLE dbo.table
END

Answer 3

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.

Answer 4

Hi,

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 TABLE myTable

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

Answer 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.

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.