I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables.
I need to create another database (production db let's say), and restore only specific file groups and its tables with its data
I've seen this answer on stackoverflow https://stackoverflow.com/questions/25841822/backup-and-restore-sql-server-database-filegroup but he's restoring the .bak file, I did like the example he provided but the bak file has all tables and I don't want that.
is that even possible ? if no, what is the purpose of the file groups then ?
I appreciate if code is provided, thank you.
1 Answer 1
Database schema for all objects is stored in the PRIMARY filegroup although user data may optionally be stored in a different filegroup. A piecemeal of individual filegroups is not intended to be used to create a new database, but rather to restore critical data before less frequently used data. That technique allows a database to be partial available for use during a restore sequence before the entire restore is complete.
if no, what is the purpose of the file groups then ?
In addition to piecemeal restores, user-defined filegroups allow you to control placement of individual tables, indexes, and partitions. This allows the underlying files to be placed on different underlying storage devices such as in a scenario where isolated storage provides a performance improvement (e.g. segregate random and sequential access) or with tiered storage (e.g. archive data on slower, less expensive media).