1

I have a db with size of more than 300 GB. But I have created two FILEGROUPS and the secondary filegroup contains only 2 tables. I want to take the backup for the that particular secondary filegroup and is it possible and restore or mount the backup file into a whole different DB and assuming this destination DB has a primary FILEGROUP intact.

I have tried taking the backup and the backup is taking successfully, But the restore is failing with the error message

Msg 3154, Level 16, State 4, Line 115
The backup set holds a backup of a database other than the existing 'TargetDB' database.
Msg 3013, Level 16, State 1, Line 115
RESTORE DATABASE is terminating abnormally.

Still I am not sure the backup is file is authentic or not.

Gourav Priyadarshi
  • 19
  • 1

2 Answers2

2

[...] is it possible and restore or mount the backup file into a whole different DB and assuming this destination DB has a primary FILEGROUP intact.

You can do a piecemeal restore, however it must be to a new database that doesn't currently exist, or to the current database which would overwrite the current database and not what you want.

You cannot do a piecemeal restore to another already online database that is not a copy (whether via restore, attach, etc.) of the database the backup belongs. The error you received is the correct error for this scenario.

To reiterate, your options are:

  1. Restore the backup piecemeal to a new database
  2. Restore over an existing copy of the database
Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91
2

At a guess you're trying to do something like a table-level restore.

As noted, this is not possible natively in SQL Server. It is however something everyone wants to do at some point or another. See other posts in this forum for details but TL;DR:

  1. Restore the backup piecemeal to a new database
  2. Logically update your target tables from the restored copy

Optionally for your specific case you could set the target DB filegroup read-only while you're figuring out what logical steps to take and physically restoring the copy & set the restored copy read-only immediately upon recovery to minimize out-of-band drift.

Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49