|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Clean out (Reinitialize) SQL Server Database?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 - What are you actually trying to accomplish here? If you drop all of the
database objects, what are you trying to preserve for future use? ================================================== People who want to share their religious views with you almost never want you to share yours with them. -Dave Barry, author and columnist (1947- ) *** Sent via Developersdex http://www.developersdex.com *** > What are you actually trying to accomplish here? If you drop all of the I can't speak for the OP but I've done this in SQL 2000 as an alternative to > database objects, what are you trying to preserve for future use? DROP/CREATE DATABASE. This is significantly faster when the database is large. However, in SQL 2005, extent formatting is deferred so that reason doesn't apply. In SQL 2005, DROP/CREATE is better/faster as long as the user has CREATE DATABASE permissions. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Stephen Hendricks" <shendri***@datalabs.com> wrote in message news:uIUs5efHGHA.916@TK2MSFTNGP10.phx.gbl... > What are you actually trying to accomplish here? If you drop all of the > database objects, what are you trying to preserve for future use? > > ================================================== > People who want to share their religious views with you almost never > want you to share yours with them. -Dave Barry, author and columnist > (1947- ) > > *** Sent via Developersdex http://www.developersdex.com *** 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,table_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. -- Show quote---------------------------------------------------------------------------- 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 - > 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 -- Show quoteHope 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 - > Joseph
you can back up an empty database, then restore the backup against the database you wish to clean up HIH Thanks Dan!
Your script works perfectly for us. - Joe Geretz - Show quote "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... >> 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 - >> > > Nice :)
-- Show quote---------------------------------------------------------------------------- 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... >> 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 - >> > >  |
|||||||||||||||||||||||
Other interesting topics