This is the known old issue. I was trying to make a local copy of my business database. I have First created the database and tried to restore the backup and it gave me the below error. Even when I check the Restore Option Overwrite the existing database (WITH REPLACE).
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set holds a backup of a database other than the existing 'Test' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
1) Leave the created DB as is or delete it if you already create one (I have created). Right Click on Databases in Object Explorer and select restore database option. Select the backup file and click ok. Database name and other options will auto-populate. You can change if you want.
2) Click on files tab and reset the data and log file locations if you want.
3) Click on options tab and select Overwrite the existing Database (WITH REPLACE) option as shown below.
4) Click ok and check for the restore completion. You can see the percentage of completion on the top of the window.