How to Automatically Clean a database out

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to Automatically Clean a database out

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-01-24 : 01:13:10

I am paying a database provider to feed my website with a database.
The rules of the subscription say that I cannot create new databases.

I am renting two SQL 2000 databases, one for test, and one for production.

I have decided I need to copy the production database to the test database. To do this, I find that I must write code to drop all the database objects in the test database without dropping the database itself. Then I can use DTS to copy the objects and the data over easily.

* Is this a common scenario?
* Does anyone out there have code that can drop all objects?
* Is there a better way? (I know of Backup and Restore, but then I'd need access to the Backup Device file, which I don't know if I can get to.)

Please talk to me about this. Thanks,

--SMerrill
Seattle, WA

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-01-24 : 06:46:55

Backup and restore is the way I use, works like a charm. If you have to drop all objects, you can just loop through the sysobjects table on the DB and use dynamic SQL to drop each object in turn.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-24 : 06:58:14

something like

create table #a(s varchar(128)
insert #a select name from sysobjects where type = 'U'
declare @s varchar(128), @sql varchar(1000)
select @s = ''
while @s < (select max(s) from #a)
begin
select @s = min(s) from #a where s > @s
select @sql = 'drop table ' + @s
exec (@sql)
end

Then check if everything has gone.
You may have to run this several times if you have foreign key constraints.
DO a similar thing for SPs, views, udfs, ...

Have you thought of using sql-dmo instead of dts?
See
www.nigelrivett.com
dmo transfer

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-01-24 : 13:54:21

Thanks, Nigel. I have also thought of dropping all the relationships first, then the tables ...

--SMerrill
Seattle, WA

srf
Starting Member

42 Posts

Posted - 2003-01-30 : 18:27:57

For a task like this it's simple to disable constraints on the tables rather than drop each one.

alter table yourtablename nocheck constraint all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-01-30 : 18:39:24

I second the backup and restore way. It's gotta be the easiest way to do this. Let us know if you need assistance with it.