How to completely clean out a SQL Server 2005 database?

Question

I made a copy of a DB that is used for a web app to make a new instance of this web app. I am wondering how do I remove all data and transactions and what not so that it is just a clean empty shell of tables ready to be written with new data?

Answer 1

You may want to consider just generating a t-sql script that only includes the structure from your existing database. The SQL Server Management Console makes this very easy, as you just need to right click on your original database, select 'tasks->generate scripts'. From there, just click through the defaults and select the objects that you want to duplicate (tables, etc).

This generates a nice T-SQL script that you can apply to any blank database, giving you the structure that you are looking for without the data. To me, this seems to be a more appropriate option as compared to truncation.

Answer 2

This article presents store-procedure without any of the mentioned problems.

The key is to disable referential integrity :)

Answer 3

run this script:

select 'TRUNCATE TABLE ' + name from sysobjects where xtype='U'

and then paste the results into a new script and run that

(And for God's sake, be careful!) :)

EDIT

From comments it seems TRUNCATE can't delete rows from tables with foreign keys.

You could use

select 'DELETE FROM ' + name from sysobjects where xtype='U'

and you would also have to rearrange the output to delete from child tables first. Others have suggested scripting a clean database and that is probably a better idea TBH.

Answer 4

Uncomment out the -- to actually run... BE CAREFUL!!

Declare @t varchar (1024)
Declare tbl_cur cursor for  
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT  from tbl_cur INTO @t

WHILE @@FETCH_STATUS = 0
BEGIN
--EXEC ('TRUNCATE TABLE '+ @t)
FETCH NEXT  from tbl_cur INTO @t
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

EDIT: In answer to the comment question... damn good question. I imagine you could find all the foreign keys and save them off

SELECT 'ALTER TABLE ' + b.TABLE_NAME + ' WITH CHECK ADD CONSTRAINT [' + a.CONSTRAINT_NAME + '] FOREIGN KEY ' 
    + c.COLUMN_NAME + ' REFERENCES [' + d.TABLE_NAME +'] ([' + e.COLUMN_NAME + '])'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
    ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS d
    ON a.UNIQUE_CONSTRAINT_NAME = d.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN (
    SELECT
    f.TABLE_NAME,
    g.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS f
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE g
    ON f.CONSTRAINT_NAME = g.CONSTRAINT_NAME
WHERE f.CONSTRAINT_TYPE = 'PRIMARY KEY'
) e
ON e.TABLE_NAME = d.TABLE_NAME
ORDER BY a.CONSTRAINT_NAME

and then you could drop all of them (I dont believe it matters in which order you drop the constraints)

SELECT 'ALTER TABLE ' + col.TABLE_NAME + ' DROP CONSTRAINT ' + u.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS col
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    ON col.TABLE_NAME = u.TABLE_NAME
    AND col.COLUMN_NAME = u.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.table_constraints t
    ON u.CONSTRAINT_NAME = t.CONSTRAINT_NAME
WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY'

and THEN use the first cursor to truncate all the tables. Then you can use the results of the script you saved off to recreate all of the FK relationships.

Answer 5

you would just truncate each table as in

use [dbname]
truncate table [table]

where [dbname] is the name of the copied database, and you would copy the 2nd line for each table in the database.

I'm sure with about 5-10 minutes, you could create a script that would read over all the available tables listed in the systables and use that information to do a while loop so you didn't have to write "truncate table [table]" for each table, but that's the general idea.

--

Ok,

To all that answered after me, I'm getting this mental "guilt" thing going on because I didn't write up that he should have created a TSQL script and re-create a database off of that.

There were several reasons why I didn't go that route.

1. You don't know what sorts of "static" data he has in the database.
2. He specifically asked about how to clear the database.
3. I don't currently know what the @OP has built into the rest of the system. there could potentially be dependencies that he needs a copy of the original in order to satisfy a condition.

Had the gentleman asked this in a different manner, I may have indeed answered like so many have and simply stated to script the database out.

Answer 6

Truncating will work if you no foreign keys defined (And if you don't please please add them).

If your using SQL Server 2005 (08 might be the same), you can generate a script for the entire database, from within Sql Server Management Studio. Right click on the database you want to script.

Then go to tasks, and generate scripts. Script out all the objects, then you can use this script to build a fresh copy of the DB based on just the schema.

Answer 7

You could create an empty database, and then use something like SQL Compare to compare your existing database against the empty one to generate scripts to recreate the database entirely from scratch.

You could also use the following SQL:

--// Switch to the database to be modified
USE DatabaseName;

--// The following commands need to be run for each table
--// You could perhaps automate this by using a cursor
--// First truncate the table and remove all data
TRUNCATE TABLE MyTable;

--// Also reset the identity seed
DBCC CHECKIDENT (MyTable, reseed, 1)

I'd recommend re-creating the database structure from scratch, rather than doing a backup-and-restore-to-new-database, as this will give you a completely clean database without any 'residue' (like stuff in the transaction log).

Answer 8

I don't know of any one step magical silver bullet command to do so, but if you want to preserve your tables/schemas, you'd probably need to script a truncate table for each.

Alternatively, you could script out the whole database and use that one script to regenerate a new database after you drop the "used" one. Making sense?

Right click on the Database you want to deal with, select Script Database As (3rd option from the top), then the option DROP and CREATE to ... at which point maybe you want to do this to a file or the clipboard and paste it somewhere.

Then, with this file handy as your script, run it to create a clean nice database.

Answer 9

Sql Server Database Publishing Wizard. Create a script with just the schema, specifying to drop the existing objects.