Error Restoring Specific Filegroups to a New Database: "The backup set holds a backup of a database other than the existing"

Question

Hello,

I am currently working with SQL Server 2019 Web Edition and need to restore specific filegroups from a production database to a new database in a development environment. Here are the steps I've followed so far, along with the errors I'm encountering:

Source Environment:

• Database Name: DB_NAME
• Filegroups: PRIMARY, DB_2024
• SQL Server Version: SQL Server 2019 Web Edition

Steps Taken:

1. Backup the Required Filegroups

I backed up the PRIMARY and DB_2024 filegroups and the transaction log using the following commands:

-- Backup PRIMARY Filegroup
BACKUP DATABASE 
FILEGROUP = 'PRIMARY'
TO DISK = 'C:\Backups\
WITH INIT;
-- Backup DB_2024 Filegroup
BACKUP DATABASE 
FILEGROUP = 'DB_2024'
TO DISK = 'C:\Backups\
WITH INIT;
-- Backup Transaction Log
BACKUP LOG 
TO DISK = 'C:\Backups\
WITH INIT;

2. Inspect the Backup Files

To get the logical file names

RESTORE FILELISTONLY 
FROM DISK = 'C:\Backups\DB_NAME_PRIMARY.bak';
RESTORE FILELISTONLY 
FROM DISK = 'C:\Backups\DB_NAME_DB_2024.bak';

3. Create an Empty Shell of the New Database

In the development environment, I created a new database and added the necessary filegroups:

-- Create the new database with the PRIMARY filegroup
CREATE DATABASE NewDatabase
ON 
    (NAME = NewDatabase_primary,
     FILENAME = 'C:\NewDatabase\NewDatabase_primary.mdf')
LOG ON
    (NAME = NewDatabase_log,
     FILENAME = 'C:\NewDatabase\NewDatabase_log.ldf');
-- Add the DB_2024 filegroup to the new database
ALTER DATABASE NewDatabase
ADD FILEGROUP DB_2024;
ALTER DATABASE NewDatabase
ADD FILE 
    (NAME = NewDatabase_DB_2024,
     FILENAME = 'C:\NewDatabase\NewDatabase_DB_2024.ndf')
TO FILEGROUP DB_2024;

4. Restore the PRIMARY Filegroup

I attempted to restore the PRIMARY filegroup:

RESTORE DATABASE NewDatabase
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\YourDatabase_PRIMARY.bak'
WITH PARTIAL, NORECOVERY,
MOVE 'YourDatabase_primary' TO 'C:\NewDatabase\NewDatabase_primary.mdf',
MOVE 'YourDatabase_log' TO 'C:\NewDatabase\NewDatabase_log.ldf';

Errors Encountered:

When I try to restore the PRIMARY filegroup, I get the following error:

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

Question:

How can I restore the specific filegroups (PRIMARY and DB_2024) from the source database (DB_NAME) to a new database (NewDatabase) in the development environment without encountering this error?

Any detailed steps or insights? or Is there any specific step do I need to follow or any Edition Limitations for this?

Answer 1

If you want to overwrite an existing database with a backup of different database , then you have to use the REPLACE option as well.

See

RESTORE Statements (Transact-SQL) => REPLACE option impact

Answer 2

Drop the database you created and try again. RESTORE DATABASE will create the database for you.