Post reply

tranferring big table

  • June 16, 2008 at 8:53 am

    #72128

    Hi,

    I want to copy one big table(300 GB approx.) present in one filegroup in one database on production to a development database. Is it possible to create a new filegroup(with the same name as in production) in development and restore the production filegroup on it. Is this possible?

    Thanks in advance,

    Manu

  • June 16, 2008 at 11:08 am

    #829356

    Yes, you can.

  • June 16, 2008 at 11:47 am

    #829384

    My understanding is that this is only do-able if it is the PRIMARY filegroup, and you restore it as the PRIMARY filegroup, that will have some obvious problems in your target database.

    The metadata for the database is only stored in the Primary filegroup, so if you backup a secondary filegroup and then restore it to another database, there is no metadata with which to interpret the contents of the restored filegroup.

    If on the other hand, you used the PRIMARY filegroup and you could restore it as the PRIMARY, it would have effectively overwritten the metadata of the existing target database, making anything that was already there inaccessible. Finally, I am not even sure that it will let you do some of these restores.

    Oracle, however, does have a feature like this called "transportable-tablespaces". I did a project a while back to emulate this feature on SQL Server for a customer who was migrating from Oracle to SQL Server. It is tricky to do, and it cannot be done using Filegroups, because of the aforementioned metadata issues. This is straight from Microsoft, as they actually referred us to the customer specifically to address this migration issue.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 16, 2008 at 12:34 pm

    #829412

    Thanks. Can you also tell me whether I need to restore transaction log back from production to development too once I restore filegroup backup?

    Manu

  • June 17, 2008 at 11:18 am

    #829942

    I don't think you need to restore the Log backup.

Viewing 5 posts - 1 through 4 (of 4 total)

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