[dba-SQLServer] Copy a database using a stored procedure or function

John,

Just a suggestion, do not ever work with Oracle.
If you think SQL Server is archaic, you have not
seen anything yet.

Take a look at SQL DMO Objects and the TRANSFER method.
That is what the wizard is using to do a database copy.

If you want to do it relatively simply:
Create the new DB.
Backup the current DB.
Restore the backup with overwrite option to the new db.

And yes, if you are willing to write some code, the three
steps can be done through a stored procedure.


Robert

At 03:10 PM 3/25/2008, you wrote:
>Date: Tue, 25 Mar 2008 15:52:56 -0400
>From: "jwcolby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Copy a database using a    storeprocedure
>         orfunction
>To: "'Discussion concerning MS SQL Server'"
>         <dba-sqlserver at databaseadvisors.com>
>Message-ID: <00b601c88eb1$d2bf09f0$0201a8c0 at M90>
>Content-Type: text/plain;       charset="us-ascii"
>
>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