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

jwcolby jwcolby at colbyconsulting.com
Tue Mar 25 16:00:56 CDT 2008


I knew if I bitched long enough someone would get tired of it and come up
with an answer.

<grin>

Thanks.  I am trying it now.

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
Elizabeth.J.Doering at wellsfargo.com
Sent: Tuesday, March 25, 2008 4:10 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Copy a database using a store procedure
orfunction

I haven't used this (being pretty new to this myself), but I'm sure some one
else here can speak to it:

E. Make a copy of a database using BACKUP and RESTORE This example uses both
the BACKUP and RESTORE statements to make a copy of the AdventureWorks
database. The MOVE statement causes the data and log file to be restored to
the specified locations. The RESTORE FILELISTONLY statement is used to
determine the number and names of the files in the database being restored.
The new copy of the database is named TestDB. For more information, see
RESTORE FILELISTONLY (Transact-SQL). 

 Copy Code
BACKUP DATABASE AdventureWorks 
   TO DISK = 'C:\AdventureWorks.bak'

RESTORE FILELISTONLY 
   FROM DISK = 'C:\AdventureWorks.bak'

RESTORE DATABASE TestDB 
   FROM DISK = 'C:\AdventureWorks.bak'
   WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'
GO
 
This is from BOL for SQL Server 2005, which is often amazingly helpful.

HTH,


Liz 


Liz Doering
elizabeth.j.doering at wellsfargo.com
612.667.2447 


This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose, or take any action based on this message or
any information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. Thank
you for your cooperation.


-----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:53 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using astoreprocedure
orfunction

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



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list