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.


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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.