Post reply

Copy Multiple Databases??

  • August 14, 2007 at 6:41 am

    #121033

    Could anyone possibly help me out with an issue I am having...

    I need to copy all my SQL2005 databases from Server1 to Server2. How can I, using SSIS, copy all the databases and not just 1 specific one (database transfer task)???

    My actual task is much deeper than this, but this is the main problem I seem to be encountering and I cannot find this anywhere on the web...

     

    Over and above the what I have mentioned, is there a way to also specify a name of the target DB the copy will go through to?

    e.g. Server 1 has 3 instances, each one with a different copy of the same DB.

    I would like to copy all 3 copies to Server 2 running 1 SQL instance, but to target DB's like DB_dev, DB_prod and DB_test.

     

    The latter section is not so crucial at the moment, but the first part is really an issues I am struggling with at the moment...

     

    Any help would be greatly appreciated.

     

    Thanking You in advance!!!!!

  • August 14, 2007 at 7:19 am

    #726758

    Try this

    Create table for connections

    coloumns:-SourceServer,SourceDatabase,DestinationServer,DestinationDatabase

    now in ssis

    Sql Task to select from connections table put query results to a variable (object type)

    then precedence constaraint to a foreach loop container enumarator type Ado and ado variable is the results variable from sqltask. Map each column to a variable SourceServer, SourceDatabase, DestinationServer, DestinationDatabase.

     

    Add a sql Object task in your foreach container. get properties of the task and select expressions press the ... should get the property expressions editor. the drop down list for property contains many items including Sourceconnection, SourceDatabase, DestinationConnection, DestinationDatabase. Select each and assign the corresponding variables as the expression (if you press ... on expression an expression builder will open where you can drag the variable from list this way you will not get an error due to a typo).

    Havent tried this but this is how i would do it. Let us know how you get on.

     

  • August 14, 2007 at 7:23 am

    #726760

    I refered to a transfer sql Objects task but will also work with a transfer sql database task

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply