Transferring database objects

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

  • The wizard will transfer objects if you check the "Run Immediately" option.

    Greg

    Greg

  • 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

  • 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

  • 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

  • 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