bak file restore error : The backup set holds a backup of a database other than the existing ‘xxx’ database

Recently I faced an issue when trying to restore a bak file .

Though the option ‘Overwrite the existing database (with Replace)’ was selected during restore process, error was not resolved.

Instead of executing the ‘Restore’ from database level, I tried to do it from query editor . This helped me to get more insight on the issue.

RESTORE DATABASE NewDatabase
FROM DISK = 'C:\Program Files\Microsoft SQL Server\AxDb_Dev01.bak'
GO

And the error message was

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "G:\MSSQL_DATA\AxDb_Dev01.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'AxDb_Dev01' cannot be restored to 'G:\MSSQL_DATA\AxDb_Dev01.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "H:\MSSQL_LOGS\AxDb_Dev01.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'AxDb_Dev01_log' cannot be restored to 'H:\MSSQL_LOGS\AxDb_Dev01_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So it is clear the back up is pointing to the path of data and log files of the source db.

Use the below command to address this issue

USE [master]
GO

RESTORE DATABASE [DbNew]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\AxDb_Dev01.bak'
WITH MOVE 'AxDb_Dev01' TO 'C:\SQL2022\AxDb_Dev01_data.MDF',
MOVE 'AxDb_Dev01_log' TO 'C:\SQL2022\AxDb_Dev01_log.LDF',
RECOVERY, REPLACE, STATS = 10;

Make sure the database in the above command doesn`t exist. It will be created when the command is executed successfully.