August 3, 2015 at 12:08 pm
Hi
I have a db with a few filegroups. I would like to 'remove' the filegroups from the db and place them on slow/tape storage in case of the need of retrieval in the future.
What would be the best strategy for this?
August 3, 2015 at 12:29 pm
You can't do that exactly. Best bet would probably be something like:
Take a backup of the primary filegroup and the archive filegroups (you need primary), put that backup onto archive storage. Then drop the tables and then the flegroups. If you need then again though, it'll be a complex process of restoring the backed up filegroups as a separate database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2015 at 3:17 am
When you say complex, what do you mean exactly 🙂
August 5, 2015 at 3:29 am
Restore they backed up primary and the archive filegroups as a new database, then if you need the data in the original database, copy it across.
Thinking about it some more, if the filegroups are read only before you take the backups, you should be able to drop the tables and take the filegroups offline, then, if the data is needed, restore the filegroups to the existing database. However any attempt to query the offline filegroup will result in errors. It's probably something you want to test carefully before doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2015 at 4:36 am
Yes I have seen it mentioned before with regards to taking a filegroup offline.
How is this done though?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply