jwcolby
jwcolby at colbyconsulting.com
Tue Mar 25 19:30:28 CDT 2008
Yes, but it is not automated. I know how to copy a database, I have been doing so for months - with the menus from the gui. I need to automate this whole thing. You don't even have to detach with the wizard. I need to do it from code. 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 7:17 PM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] Copy a database using astore procedure orfunction For a anti-T-SQL, no T-SQL batches GUI easy way to move a file... As I posted at 1:25... Pick the database in SSMS - Object Explorer Right click to open context menu > Tasks > Detach then OK The file can now be copied to wherever you want. On the new server, in SSMS-Object Explorer Pick Databases Right click to Open Context Menu > Attach Pick the file and click OK. -Paul -----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 1:25 PM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] Copy a database using a store procedure orfunction To move /copy a file use detach - copy the file - then attach. Real fast real simple. -Paul -----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 _______________________________________________ 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