1

I have create two filegroup FG1 and FG2. and i have create one table student and associated with FG1. and i have created backup for FG1 with Test.bak file .

now i wanted to restore the Test.bak enter image description here enter image description here

i am getting error like attached screen ..please advise

regard's Ravi

0
1

The database engine thinks you're trying to perform a piecemeal restore using the UI. This is the formal name of restoring individual filegroups. Sadly, it requires you restore the entire database to maintain transactional consistency (which I don't think you want to do in your scenario) and since you're not overwriting the database (as identified by Pinal Dave's answer), you're getting this error.

If you're only trying to restore that one table, you've got a couple of options available, as follows:

  1. You can restore the database as a different name and then copy the data from the restored copy into the working copy.
  2. You can store that table in a different database and if you need to revert that table, restore the database it's contained within. Of note, you are unable to maintain Referential Integrity between tables stored in different databases without some trickery (e.g. Triggers). This may not be a problem now, but it could crop up issues in the future, so just be aware of this if you do separate out this table into a different database.
0

You can attempt to restore your backup with REPLACE command.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

You can read more about it here: https://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

2
  • From the screenshot, the REPLACE option was selected when OP tried to restore.
    – RDFozz
    Jul 17 '17 at 15:03
  • Good Point. I suggest, please try this out with T-SQL if possible to OP.
    – pinaldave
    Jul 17 '17 at 15:19
0
  • Use WITH REPLACE while using the RESTORE command.

  • DROP the older database which is conflicting and restore again using RESTORE command.

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

General Script:

RESTORE DATABASE Database_name
FROM DISK = 'c:\MSSQL\test.bak'
WITH REPLACE,
MOVE 'test' TO 'e:\MSSQL\test.mdf',
MOVE 'test_log' TO 'f:\MSSQL\test.ldf';

Check this link: Error 3154 while restoring a backup using WITH REPLACE

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.