[dba-SQLServer] Copy a database usinga storedprocedure or function

jwcolby jwcolby at colbyconsulting.com
Wed Mar 26 21:33:27 CDT 2008


Paul, I do the best that I can.  I take the examples and paste them into a
query window and try and run them and they don't work.  They throw arcane
error messages (excuse me, exceedingly useful error messages that I just
don't happen to have the years of expertise to understand).  Things like 

Line 10
"error close to ,"

Paul, I don't care what you say, Error near , is NOT a useful error message.

I sit on the Access list and provide code all day.  People come asking for
stuff, I don't tell them to go read books online, I give them a piece of
working code, WITH EXPLANATION of how it works and why, as do the other
listers.  If the person needs assistance learning how to use the debug
window I give them that, as do the other listers.  If they need to know how
to OPEN the debug window, I give them that, as do the other listers.

Search the archives Paul, I have been here for about 10 years and provided
thousands of answers to questions, to provide whatever level of assistance
the person needs from nubee (me here) to advanced expert.  Look on the
downloads page Paul.  

http://www.databaseadvisors.com/downloads.asp

You will see my name on more fully working code examples than all the rest
of the members of these lists COMBINED.  I put my money in the pot to help
start Database Advisors and make these lists available to you.  I have been
on the BOD of Database Advisors since it was started.  I have served as
president of Database Advisors.  I continue to contribute cash to the
company to make these lists available to you.  

http://www.databaseadvisors.com/donations/corporate.asp

I am not someone who just comes here, contributes nothing and asks everyone
to do all my work for me.  I need help too, and I did not come here to be
lectured about how if I would just spend the time to become an admin... (or
to be lectured about spam or bulk mail).  To be honest I thought this was
going to be a quick "it's easy... Here's a few lines that do what you need".


NOW####################

The original subject was "copying a database using a stored procedure".  

I got one "click this and then click this and the database is copied".  

Yea, but I didn't ask that (and I already know that as well).

I got another from Elizabeth (Thanks very much Elizabeth) that looks like it
might actually work, and I am trying to make it work with parameters...
STILL trying to make it work with parameters.  But at least it is just a few
lines that I can actually read and understand.

>I know it's a pain to learn, but SSIS (integration services) can do just
about everything you need to do

Thanks.  Do I take this to mean that a SP cannot do this?  What exactly do I
do with that response?

>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.

Actually an answer, but not really useful in my circumstances as I politely
explained.  But thanks James, it was an actual attempt to help.

>To move /copy a file use detach - copy the file - then attach. Real fast
real simple. 

Hmmm... Did you read the question?

>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. 

OK, didn't understand a single word of that, but definitely not code that I
can drop into a SP and get a database copied.  Sounds vaguely like a
"lecture".

>Source code belongs in source code control. Developing in a gui is just
plain dangerous. 

>All I'm saying is that when you code the sproc, do it in query editor and
then save the file. Don't use Object Explorer to create new proc, save it in
the database and then >edit it by opening it from the database. It's the
same code, the difference is that the database is the destination of the
code, not where you keep it. You keep the code in >source control. 

>I do all my development from files in directories that I open, edit, and
then run. It's the fastest way to develop with SQL Server. 

>If you develop with multiple databases and multiple severs then you really
need to work this way. You open you file, connect to whichever server you
want, and then press F5. >It's fast, simple, and easy. 

>In SQL Server lingo, any submission to the database is a batch. If you
highlight a query and press F5 - that's a batch. If the batch is saved in
the database, that's a stored proc. If you've been working with SQL Server
you've been doing batches. The question is only where do you want to save
your source code? 

Hmm... Nope, no sp running yet.  DEFINITELY sounds like a lecture.

>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

BINGO, something useful.  Thanks Elizabeth!!!

>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.

OK... I am willing to write code.  Just don't know what code to write.  And
even if I did, I can't interpret the damned error messages.  ;-)

And it goes down hill from there...

So yea, for some reason I do find this list less than useful.  A full day of
lectures, and ONE response that actually looks like it might be helpful.
Thanks Elizabeth!

You guys ROCK!  But by and large a huge waste of everybody's time don't you
agree?  Having done this day, I can tell you that there is not much point in
doing another such day.

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: Wednesday, March 26, 2008 5:17 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database usinga storedprocedure or
function

>> SQL Server (interface) is BARELY superior to dbase II in a few areas 
>> and
much worse in
others.

I don't believe you've ever used the SSMS in the way I've been promoting. If
you did, you'd be in love with how easy and quickly you get the work done.
So you have no right complaining about what you refuse to try. 

...

Now, I have a chapter to finish. 

-Paul  




More information about the dba-SQLServer mailing list