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

Paul Nielsen pauln at sqlserverbible.com
Tue Mar 25 14:24:58 CDT 2008


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?

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





More information about the dba-SQLServer mailing list