Post reply

Restore to a different File Group

  • February 17, 2012 at 12:29 am

    #253117

    Is there a way to perform a restore to a different file group?

    I have a database "DataBase1" whose default primary group is PRIMARY. I created another database "DataBase2" I then created a file group in "Database2" "FileGroup1" I need to restore Database1 to Database2 and the contents of PRIMARY filegroup should then be transfered to "FileGroup1"

    Is there a way to do this straight forward through restoration?

    There is a way to transfer all of the objects but it is not straight forward.

    1. Restore Database1 on Database2.

    2. create "FileGroup1" on Database2.

    3. Create a temporary clustered index on all tables specifying the new filegroup to transfer the contents to the new filegroup.

    CREATE CLUSTERED INDEX IDX_TEMP ON [dbo].[TABLE1](COLUMN1)

    ON FileGroup1

    GO

    4. Drop the temporary clustered index

    DROP INDEX IDX_TEMP ON [dbo].[Table1]

    GO

    Is there a way to restore to a different file group. Opinions and suggestions are most welcome.

  • February 17, 2012 at 1:05 am

    #1448306

    I'm pretty sure this cannot be done, as I believe you have to restore the filegroup back to the original database it was backed up from, as I had an issue where I wanted to move a data from FG4 in LiveDB to FG4 in DevDB and it wouldnt let me.

    Also as its the primary filegroup I'm going to go out on a whim and say its deffinatly not possible as you would end up with two of each of the sys tables.

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

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