question

mpdillon avatar image
mpdillon asked

Restore database backup to a different database

I need to restore our production database, Data_02 to a different testing database, Data_100. Data_100 was created using SSMS. This in SQL 2008 R2.

The production database has a full text catalog associated with it. I believe that is what is causing me the issue. Usually, I would just use SSMS to restore. However with the FTC, that was not working.

I am now trying to restore using commands. Below is the command I tried (paths simplified).

RESTORE DATABASE Data_100 from disk = N'E:\Data_02 Test Oct 02 2019.bak'
WITH MOVE 'ftrow_FTC_titles{4E13037C-2F3F-49A3-B194-228F86A4F958}.ndf' TO 'E:\ftrow_FTCData_100.ndf'

This cause the error:

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

Would someone please assist me with the code to properly restore a database to a different database with a full text catalog on the same server.

Thank you,

Pat

backup-restore
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

Try using WITH REPLACE as you are effectively overwriting the existing Data_100 database with a backup of Data_02 database.

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#REPLACEoption

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.