MS SQL Server DTS - Clean out (Reinitialize) SQL Server Database?

Author

Clean out (Reinitialize) SQL Server Database?

Joseph Geretz

2006-01-20, 1:23 pm

It there an easy way to 'clean out' a SQL Server database? I need to get a
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)

(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)

Thanks for your help!

- Joseph Geretz -

Louis Davidson

2006-01-20, 8:24 pm

You could easily write a proc with a little cursor to do this using

2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:

sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,t
able_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"Joseph Geretz" <jgeretz@nospam.com> wrote in message
news:ezib0ueHGHA.2040@TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
>
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
>
> Thanks for your help!
>
> - Joseph Geretz -
>

Dan Guzman

2006-01-21, 1:23 pm

Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.

http://tinyurl.com/9t9m6

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Joseph Geretz" <jgeretz@nospam.com> wrote in message
news:ezib0ueHGHA.2040@TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
>
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
>
> Thanks for your help!
>
> - Joseph Geretz -
>

Alexander Kuznetsov

2006-01-21, 1:23 pm

Joseph

you can back up an empty database, then restore the backup against the
database you wish to clean up

HIH

Joseph Geretz

2006-01-22, 1:23 pm

Thanks Dan!

Your script works perfectly for us.

- Joe Geretz -

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
>
> http://tinyurl.com/9t9m6
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Joseph Geretz" <jgeretz@nospam.com> wrote in message
> news:ezib0ueHGHA.2040@TK2MSFTNGP14.phx.gbl...
>
>

Louis Davidson

2006-01-22, 8:23 pm

Nice :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
>
> http://tinyurl.com/9t9m6
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Joseph Geretz" <jgeretz@nospam.com> wrote in message
> news:ezib0ueHGHA.2040@TK2MSFTNGP14.phx.gbl...
>
>

Sponsored Links