Restore from a filegroup to a different database

Question

Hello folks,

      I have a huge database in production that needs to be backed up on daily basis. However, parts of that database are not very important. My plan to tackle this situation is to create a filegroup and move some of the tables to that filegroup and backup that specific filegroup on a daily basis. When I'm trying to restore it on a different server on a database with the same name and filegroups, I get the below error. I tried "with replace" and "partial" options as well. Nothing seemed to work in this scenario. below is also the script i'm trying to run. Any help is greatly appreciated and thanks in advance.

Error:

Msg 3154, Level 16, State 4, Line 2
The backup set holds a backup of a database other than the existing 'Database1' database.

RESTORE DATABASE [Database1]
FILEGROUP = 'FG_Data_rollup'
FROM  DISK = N'C:\SQL DATA\Database1_data_rollup.bak'
WITH MOVE 'Database1' TO 'C:\SQL Data\Database1.mdf',
MOVE 'FG_DATA_rollup' TO 'C:\SQL Data\FG_DATA_rollup.ndf',
MOVE 'FG_DATA_rollup0' TO 'C:\SQL Data\FG_DATA_rollup0.ndf',
MOVE 'Database1_Log' TO 'C:\SQL Data\Database1_log.ldf',
 FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, partial
GO

Thank you.

---

----- Vinod

Answer 1

Hello Vinod,

Pinal Dave has written an article on SQL server error mesage 3154, you can read it from here: http://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/

---

Read my blog (Blog)
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

thank you both for the replies. The issue in my case is, the database is in Simple recovery model. Both the above examples explains only when the Database is in Full recovery model.

---

----- Vinod

Answer 4

Hi vins84,

If the database already exists in the server you can either use the With Replace option to overwrite it or else you can drop that database and restore.

A similar thread could help you: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/5176c567-de02-46d9-a61b-479201d4c579.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.