Wil Wil - 4 months ago 13
SQL Question

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

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 Source

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(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

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