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

Vinith 45 Reputation points
2024-06-21T08:44:27.7233333+00:00

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;
  1. 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';


  1. 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;
  1. 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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,332 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2024-06-21T10:12:21.4566667+00:00

    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


  2. Erland Sommarskog 106.5K Reputation points
    2024-06-21T22:20:43.35+00:00

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