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