none
Transfer Database Task on Database with multiple filegroups

    Question

  • Hello all!!
    i have a SSIS package that has a "Transfer database task" but the database I'm trying to transfer in offline mode (detach copy / attach) has multiple files with multiple file groups.
    how can I set the values for "SourceDatabaseFiles" and "DestinationDatabasefiles" to accept more than one file (.mdf, and all other .ndf) ?
    note: the mdf and ndf files are located on different drives.

    I'm working with visual studio 2008 / sql server 2005.

    thanks,
    Tamer
    Tuesday, November 10, 2009 4:58 PM

Answers

  • yes, I'm familiar with these steps, it's just the multiple database files was kind of tricky, but I got it now, basically the sourceDatabaseFiles and destinationDatabaseFiles follow this format:

    db file name,from path,to path;db file name,from path,to path;db file name,from path,to path;db file name,from path,to path; and repeat as many as you want...

    thanks,
    Tamer
    • Marked as answer by Tamer Yousef Wednesday, November 11, 2009 8:57 PM
    Wednesday, November 11, 2009 6:45 PM

All replies

  • This can be done using Transfer database tasks. Refer this page: http://technet.microsoft.com/en-us/library/ms189371.aspx
    Please followthe below steps,
    1. Drag a Transfer database task, right click and say edit.
    2. In the Database tab, configure as the following,
    3. Chose a source and destination server under Connection properties
    4. Select the action as Copy/Move
    5. Method as DatabaseOffline for detach and attach
    6. Provide the name of the existing source database under SourceDatabaseName properties
    7. After doing so you Click the browse button to select the database files. To automatically populate these fields with the database file names and locations, specify the SourceConnection, SourceDatabaseName, and SourceDatabaseFiles first.
    8. Provide the name of the existing destination database under DestinationDatabaseName properties.
    9. Specifies the names and locations of the database files on the destination server.

    The order mentioned above it important than the order in which it is layed out in the tasks.

     


    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    • Proposed as answer by Bharani 3010 Wednesday, November 11, 2009 10:17 AM
    • Unproposed as answer by Tamer Yousef Wednesday, November 11, 2009 8:58 PM
    • Proposed as answer by Bharani 3010 Friday, November 13, 2009 6:30 AM
    Wednesday, November 11, 2009 5:57 AM
  • yes, I'm familiar with these steps, it's just the multiple database files was kind of tricky, but I got it now, basically the sourceDatabaseFiles and destinationDatabaseFiles follow this format:

    db file name,from path,to path;db file name,from path,to path;db file name,from path,to path;db file name,from path,to path; and repeat as many as you want...

    thanks,
    Tamer
    • Marked as answer by Tamer Yousef Wednesday, November 11, 2009 8:57 PM
    Wednesday, November 11, 2009 6:45 PM
  • What you say is valid! but i guess you didnt gt my point.

    If you do follow the steps you dont need to remember this format at al....

    Just do the folowing and see what happens,
    1.  Chose a source server
    2. Select the action as Copy/Move
    3. Method as DatabaseOffline for detach and attach
    4. Provide the name of the existing source database under SourceDatabaseName properties
    5. After doing so you Click the browse button and click on OK.

    You will find the files details populated in the above format.



    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    Thursday, November 12, 2009 5:41 AM