[dba-SQLServer] Copy a database using a storeprocedure orfunction

Francisco Tapia fhtapia at gmail.com
Tue Mar 25 17:59:37 CDT 2008


John,
  Since you do not want to be a TSQL wiz, there are ways for you to get
things done really quick.  One such way is to use a tool from Red-Gate.
It's called Sql Compare, you can litterally point it from source to
destination db, it will look at all such objects and copy over any new
object, or even bring over modified objects, (ie, changes to views) to the
target db.

http://www.red-gate.com/
--
Francisco

On Tue, Mar 25, 2008 at 12:52 PM, jwcolby <jwcolby at colbyconsulting.com>
wrote:

> I have to tell you Paul that your world is not accessible to the average
> Joe
> trying to get work done.  I am not a SQL Server admin or a Sql Server
> anything, I am a guy trying to fill an order.   I haven't the slightest
> interest in spending the next 10 years becoming a command line guru.  I
> want
> to write a line of code
>
> Copy "SomeDatabaseName", "SomeOtherDatabaseName"
>
> If it is more complex than that then it is useless to me.  In any given
> week, I support a call center application in Connecticut (via a tunnel and
> RDT), a pair of databases in a town just north of Phili (via RDT), my
> servers, pick up information on building virtual machines, download and
> install firewalls, and fill orders on a largish database shipping email
> lists to clients etc.  I am just me, running a business.
>
> We live in 2008, not 1958.  I am not feeding punched cards into a card
> hopper, I am trying to solve a problem.  Anything that stands in the way
> of
> solving that problem is a curse, not a blessing.  SQL Server simply sucks
> compared to almost anything else out there in 2008.  Batches?  Give me a
> break!!!  Command lines?  I did that in 1982 with CPM, I do not expect to
> do
> that in 2008 with SQL Server.  TWENTY FIVE YEARS AGO I DID COMMAND LINES.
> My computer is three orders of magnitude more powerful than 1982.  My
> programming languages are several orders of magnitude more powerful than
> 1982.  My database is several orders of magnitude more powerful than 1982.
> And yet I am supposed to use batches and command lines to control it?????
> That is like programming in Fortran by lining up the columns of a line of
> code in the columns of a punched card!  I did that (in 1984).
>
> You may very well love that crap, but it is not useful to the ordinary
> Joe.
> Using primitive tools is not a sign of intelligence (or manliness).  It is
> merely a sign of the sorry state of the tool.
>
> Just my opinion of course.  Trying to get WORK done here.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
> Nielsen
> Sent: Tuesday, March 25, 2008 3:25 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Copy a database using a storeprocedure
> orfunction
>
> Never Ever build any objects using SSMS Object Explorer. Develop
> everything
> - DDL/Schema, procs, unit test inserts, and proc test batches ALL with
> scripts stored as .sql files in the file system and checked into source
> control.
>
>
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, March 25, 2008 1:09 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Copy a database using a store procedure
> orfunction
>
> James,
>
> That is an interesting solution.  My main objection to that (other than
> being so 1950s-ish) is it doesn't adapt to changes, i.e. any time I add a
> new view or stored procedure etc I have to rebuild the script.  I just
> want
> to
>
> Copy "SomeDatabase", "NewName"
>
> This is the kind of thing that drives me crazy about SQL Server.  It has
> the
> IQ of a mouse when it comes to programming.  I'm telling you the .
> Language
> of Dbase II was like Einstein compared to what you have to do with SQL
> Server.
>
> "Generate scripts"?  The database is a single file (OK it could be more,
> but
> still...), what is so damned hard about copying that and telling it that
> it
> has a new name?  To make matters worse, there is a wizard that does it.
>  Why
> can't I just call whatever function that wizard calls and pass in the
> parameters that the wizard gathers and copy my database?
> 19
> When I want pain, I go to my mistress downtown and she uses a cat-o-nine.
> When I really want some TORTURE I fire up SQL Server and try and do some
> work.
>
> ;-)
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of James
> Barash
> Sent: Tuesday, March 25, 2008 2:40 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Copy a database using a store procedure
> orfunction
>
> John:
>
> If you only need to copy the design of the database but not the data, you
> can use the scripting wizard to create a script of all the objects and use
> that to make the new database. If you are using SQL Server 2005,
> Right-Click
> on the database, select Tasks -> Generate Scripts. Then you can choose to
> script all the objects, including Tables, Views, Stored Procedures, UDF,
> Users, etc; or you can choose individual objects. Once you've created the
> script, add the appropriate Create Database statement at the top and save
> the script to a file and you will be able to build as many database as you
> need from the same template.
> Hope this helps.
>
> James Barash
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, March 25, 2008 2:06 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: [dba-SQLServer] Copy a database using a store procedure or
> function
>
> I have built up a template database which has views that pull data out of
> many different databases / tables.  Additionally I now have user stored
> procedures which perform processing steps.  In real life I manually copy
> this template database to a new name using the copy database wizard, and
> then run the stored procedures and stuff to build up a data order for
> shipment.
>
> What I really need to do is have a method of copying this database to a
> new
> name and then "use" that database to launch the various stored procedures
> inside of it to fill the tables, build the data and export the data.
>
> Is there a function or sp which can copy a database to a new name?  When I
> Google I get about a million "use the copy database wizard" answers.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> __________ NOD32 2971 (20080325) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...



More information about the dba-SQLServer mailing list