locked
Move or Restore a Filegroup to a different database RRS feed

  • Question

  • Hi There,

    This is regarding moving or restoring SQL Server 2005 FileGroup to a different new Database on the same SQL server.

    Requirement: Our database size is 2.5 TB and 1 TB of it is a single table, LogTable. This table is in a single file group, FGLogTable. Ofcourse the backup size is getting longer and longer, spanning to working hours. What we are thinking is to move this table/File group to a different database. Hope this will help us to reduce the backup time a lot.

    Options: Create a secondary Empty database, with the same FileGroups. Detach/Copy the FGLogTable and Attach/Restore to the Secondary database. I know when we retore the FileGroup it is going to check for the GUID of the files and file group. Is there any work around so that we can Restore this FGLogTable filegroup to a different database? We really do not want to INSERT-TRUNCATE route, because the number of records in the table is 2 BN.

    Thanks,
    Sams


    Sams
    Thursday, January 29, 2009 11:34 PM

Answers

  • Interesting question Tim.  In theory, it ought to be possible, but I know of no mechanism that SQL Server makes available that would allow you to do this.  You'd need to update system objects in the original database (as a number of objects are now disappearing, not to mention the filegroup itself) and the destination database. 

    You would also get into some interesting log consistency issues - to restore database B, you'd you'd have to start restoring from a full backup from database A until the point where the FG is moved from A to B, and then you'd need to start restoring log backups from A and B until the FG was up to date.  But if you have LSNs that exist in both databases...

    I'm sure the SQL Server team could figure out a method of doing it, but it's probably easier to create a seperate database instead of a filegroup.  You can then easily switch between databases (either by renaming them or dropping and reattaching).  The only drawback to this method is you'd need to drop users out of the database before you could do this.
    • Marked as answer by Sams007 Sunday, February 28, 2010 10:08 PM
    Tuesday, November 17, 2009 11:13 PM
    Answerer

All replies

  • A few thoughts:

    Why not just do filegroup backups on your main database?  Back up the log table on a different schedule, for instance.

    Are you going to be copying data over on a regular basis, or is this a one time move?  If the former, you may be able to take advantage of partitioned tables and the SWITCH operator in SQL Server 2005.


    Aaron Alton | thehobt.blogspot.com
    • Proposed as answer by Aaron Alton Friday, March 6, 2009 4:47 AM
    Friday, January 30, 2009 5:19 AM
  • I'll second Aaron's suggestion- this is a perfect opportunity to use filegroups.  You can take a full filegroup backup of FGLogTable once per month, and thereafter just perform nightly differential filegroup backups (and your usual log backups).  For the other filegroup, just perform full backups of that single filegroup.

    You might also consider archiving the FGLogTable filegroup.  You could rename the table (LogTable2008), create a new filegroup named FGLogTable2, create a new LogTable2009 table on FGLogTable2, and create a view to union the two tables together.  Then mark the old filegroup readonly (perhaps rebuild indexes first), back it up, and never have to touch it again.  You could even put it on a NTFS compressed folder to shrink the size down. 
    • Proposed as answer by Aaron Alton Friday, March 6, 2009 4:47 AM
    Friday, January 30, 2009 10:28 AM
    Answerer
  • If I have two replica databases on different instances (same name, structure, partitioning keys, etc) with data partitioned among filegroups, is it possible to move a filegroup from one database to the other database?   ie, I want to basically perform a "SWITCH" and move the partitioned data on a filegroup from one database over to another. 


    Tim Chapman www.sqlservernation.com
    Tuesday, November 17, 2009 7:13 PM
  • Interesting question Tim.  In theory, it ought to be possible, but I know of no mechanism that SQL Server makes available that would allow you to do this.  You'd need to update system objects in the original database (as a number of objects are now disappearing, not to mention the filegroup itself) and the destination database. 

    You would also get into some interesting log consistency issues - to restore database B, you'd you'd have to start restoring from a full backup from database A until the point where the FG is moved from A to B, and then you'd need to start restoring log backups from A and B until the FG was up to date.  But if you have LSNs that exist in both databases...

    I'm sure the SQL Server team could figure out a method of doing it, but it's probably easier to create a seperate database instead of a filegroup.  You can then easily switch between databases (either by renaming them or dropping and reattaching).  The only drawback to this method is you'd need to drop users out of the database before you could do this.
    • Marked as answer by Sams007 Sunday, February 28, 2010 10:08 PM
    Tuesday, November 17, 2009 11:13 PM
    Answerer