[dba-SQLServer] Copy a database using astore procedure orfunction

Paul Nielsen pauln at sqlserverbible.com
Wed Mar 26 17:16:09 CDT 2008


I am trying to show John how to develop a template database the easy way.
For several messages I presented a gentle "this way is easier" approach,
happy to help, and was insulted at every message. 

Enough is enough. SQL Server is not a horse and buggy, not 1985, and my
methods are not stone-age. He has no right to insult what he won't try. 

It's not sink or swim method. Insisting on SQL Server development using only
clicks is the sink or swim. Learning to save a script is the easy way.

John's big problem is that he wants a template database but his click method
is the opposite of the easy to re-deploy template approach. 

-Paul 




-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Tuesday, March 25, 2008 7:34 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Copy a database using astore procedure
orfunction

This is going to be a difficult message to phrase correctly. I want to be
extremely careful here because I am extremely uncomfortable with Paul
Nielsen's perspective and attitude. With anyone whose address is sqlbible
one must be careful, but I need to say that Paul's attitude is not in the
realm of sharing information with newbies, but rather in the realm of "I
came from the command line and therefore you should learn it first". Perhaps
that is not what Paul intends, but that is how I'm perceiving his line here.
I myself take a different path. I think "use the wizards while you're
learning, as much as possible", and trace what happened as a result of your
instructions -- if and when you're ready for that. To say, "Don't use the
wizards" is to negate all the work the development team put into this part
of the product, not to mention that the wizards get things done. This is not
to say that I am ready to marry the wizard-builders, but it is to appreciate
their efforts. As it happens, I use tools from ApexSQL and Red Gate that
solve problems the built-ins do not.

Paul has an excellent point when it comes to writing cert-exams. These
typically do not want to know which things to click to do a given job; they
want the underlying SQL, and this is a good thing. But you can get things
done using the wizards, and learn how they do it at the same time. Paul
seems to be ignoring the growth path, and instead (as my father did to me)
that the best way to learn to swim is to throw you off the end of the pier.
As a victim of that teaching strategy, I do protest, and suggest another
way.

JC's point is valid, and Paul's command-line objections are valid too --
once you get to a certain point, but definitely not at the outset. Paul is
perpetuating the "priesthood" mentality, and I for one take serious
objection to that attitude, but I am attempting to do so gracefully and
merely suggest that it overlooks the tutorial and
getting-things-done-right-now phase.

I hope that these objections were phrased gently enough so as not to start a
war.

Arthur


On 3/25/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> Yes, but it is not automated.  I know how to copy a database, I have been
> doing so for months - with the menus from the gui.  I need to automate
> this
> whole thing.  You don't even have to detach with the wizard.
>
> I need to do it from code.
>
>
>
> 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 7:17 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Copy a database using astore procedure
> orfunction
>
>
> For a anti-T-SQL, no T-SQL batches GUI easy way to move a file...
>
> As I posted at 1:25...
>
> Pick the database in SSMS - Object Explorer
>
> Right click to open context menu > Tasks > Detach then OK
>
> The file can now be copied to wherever you want.
>
> On the new server, in SSMS-Object Explorer
>
> Pick Databases
>
> Right click to Open Context Menu > Attach
>
> Pick the file and click OK.
>
> -Paul
>
> -----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 1:25 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Copy a database using a store procedure
> orfunction
>
> To move /copy a file use detach - copy the file - then attach. Real fast
> real simple.
>
> -Paul
>
>
> -----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?
>
> 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
>
>
> __________ 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
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


__________ NOD32 2976 (20080326) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com





More information about the dba-SQLServer mailing list