December 12, 2006 at 12:18 pm
What is the best method of transferring views and stored procedures from one db on one server to a db on another server? Mind you, I only need the object itself, i.e. the script, not the output. Total items I need to move are about 50 (views and procs).
I thought that the "copy objects and data between SQL server databases" in the Export task would do the trick, but it only created a copy of the script on my local HDD -- perhaps I'm missing a step here. I was hoping to find something that would physically copy views and stored procs from one db to another, so that I wouldn't have to re-create all 20 views and all 30 stored procs all over again on the other server.
December 12, 2006 at 12:38 pm
The wizard will transfer objects if you check the "Run Immediately" option.
Greg
Greg
December 12, 2006 at 2:45 pm
If you go to enterprise manager, right click the db name and choose "all tasks" > "generate sql scripts", a dialogue box will appear allowing you to choose the objects which you wish to be created. You can save the script locally, copy and paste it to the other server and then run it from the query analyser. It doesn't seem like you need to copy over tables, however if you end up doing so, make sure you select the option to copy over keys and indices.
Olja
December 12, 2006 at 3:01 pm
As mentioned it can be done both ways...
When you use import export wizard you have specify the destination server name not file name...
I think you have given the file name so that it copied to your HDD.
Better way for this is as Olja mention script through EM. You can save the copy for future use or refernce and you can run the script based on your need (part or whole)...
MohammedU
Microsoft SQL Server MVP
December 13, 2006 at 3:20 am
The tricky part about transferring objects is that DTS etc. does not take dependency order into account meaning that you'll have to do some manual tweaking for anything other than stored procedures. There are several tools on the market that can solve your problem easily - my favorite is DB Ghost from Innovartis http://www.dbghost.com.
Kind regards,
Malcolm
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
December 14, 2006 at 6:38 am
Thanks for the advice on this one folks! I ended up generating the scripts for each and transferred them over per Olja's suggestion and it worked like a charm! (also seemed to be the safest way)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply