1

I have a SQL server database with 5 filegroups i want to backup 2 filegroups(one contains filestream)only and restore them to different existing database.

Hossam Elsagheer
  • 151
  • 1
  • 5
  • 15
  • What do you mean by saying "to different EXISTING database"? Every restore recreates the files it restores, do you mean your "different" database has the same structure? (I mean number of data files and their readwrite/readonly state? – sepupic Sep 14 '17 at 06:31
  • Another database that have different number and names of files and filegroups – Hossam Elsagheer Sep 14 '17 at 08:45
  • When you'll restore into that database, noone on it's files will be accessible. The primary mdf file will be rewritten, and newly restored database will have no idea of what files did it has before restore. It will know only about the files of original db that was backuped – sepupic Sep 14 '17 at 08:49
  • 1
    So if you wanted smth like "to add some filegroups to existing database restoring them from another db", it's not possible – sepupic Sep 14 '17 at 08:50

1 Answers1

3

What you are asking about is Piecemeal Restores (SQL Server)

You can restore primary + all readwrite fg + any of readonly filegroups in you are in simple recovery model or you can restore primary + any other filegroup(s) if you are in full recovery model.

As the first filegroup to restore is always primary filegroup, it will replace your "different existing database" primary data file and log (in your restore command you should use with move that points to your existing mdf + log files) and every subsequent restore will overwrite other files, there will remain nothing from your "existing" database, so there is no sense to restore to a "different existing database": you will be able to bring online only the filegroups you are restored and the database will know nothing about remaining files of "existing database". So it's just the same as to restore to just another(non existing) database.

Here is the restore sequence example for primary + readonly fg in simple recovery model Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

And here is the example for full recovery model Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • My case is that i have two databases db1(filegroups fg1,fg2,fg3) and another on db2(filegroups fg4,fg5,fg6) , i want to backup fg2 from db1 and restore it to db2 – Hossam Elsagheer Sep 14 '17 at 08:51
  • I already answered you, it's impossible. Every piecemeal restore starts by mandatory restore of primary filegroup. And fg1 from db1 will know that this db has only fg1 and fg2. Your fg4, fg5, fg6 will not be part of restored db – sepupic Sep 14 '17 at 08:55
  • You could restore only some fg without restoring primary TO THE SAME database, but it's not your case. And when you restore to another db, the first fg to restore is always PRIMARY – sepupic Sep 14 '17 at 08:56