SQL Server 2008 Restore - Error 3154: The backup set holds a backup of a database other than the existing database.

I came across an issue today where i needed to restore a backup of a database. The database backup needed to be restored to a database with a different name, but with the original database still in production on the same server. 

When you attempt it you get the error below.

This can create an issue because the backup wants to restore to its original file location. The error isn't really very helpful.


To recreate the issue:

1. Backup database DBOriginal1 to DBO1.bak
2. Right click on database you want to restore to and select Restore > Restore Files & Groups.
3. In the Source for Restore section, select From Device. Click the ... button and select the DBO1.bak file.
4. On the Options page, tick the box Overwrite the existing database (WITH REPLACE).
5. Click OK
6. The backup set holds a backup of a database other than the existing database.

Using the wizards built into SQL Server Management Studio (SSMS) seems to be inaccurate and often they have bugs. 

---

Resolution

To get around this we have two options.

The simplest option is, on the Options Page of the Restore wizard, we need to change the Restore Database Files as: 

1. In the Restore Database Files as: area, change the Restore as name to the name of the new database (DBO2.mdf, DBO2_log.ldf)  
2. Click OK.
3. This will push the backup to the correct database. Not sure why you have to tell the Wizard the exact path of the files, even after selecting the database we want to restore to.

The other option is to create a SQL script to restore the files. Personally i prefer this option as you know exactly what is happening and you can see what you are going to do. I don't trust the wizards very much :p

RESTORE DATABASE DBO2
  FROM DISK = 'c:\SQL Backups\DBO1.bak'
  WITH REPLACE,
  MOVE 'DBO1' TO 'c:\SQL Data\DBO2.mdf',
  MOVE 'DBO1_log' TO 'c:\SQL Logs\DBO2.ldf';

Hope this helps someone else!