During the process of applying changes to a production database - let's call it Change Management (pick your ITIL or COBIT method accordingly), we should always go through a thorough testing process on an exact copy and environment too, if possible, of the production database itself.  Straight to the point, there is a simple method to avoid Error 3154: The backup set holds a backup of a database other than the existing database message or, in other words, the non-matching backup set restore failure that I have been stumped on occasionly, until I remember to edit the script as below.

Here's a typical Restore script run in SQL Server 2008/5 - which will fail because the media set is not matching:

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' FROM DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10  -- replace is because you are restoring over

First create an empty database with the desired Test nomenclature for your respective environment.  Notice, that if we take out both the File=  headers for the restore (struck out below), we will be able to successfully restore the backup file even if the database is from an entirely different server.

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' FROM DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10

Now, in another scenario, if we are doing this on databases being used in production, and require a quick restore, there will possibly be a log file (tail of the log file error) problem that can be mitigated by first doing a log backup prior to full restore, switching the database to single user mode, then restoring over top of the existing database (no replace needed below since it's on the same server, same DB) with the specific restore point, finally followed by a switch back to Multi User mode.

use master
GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'MyDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDatabase')

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDatabase'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDatabase] FROM
DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10

GO

ALTER DATABASE [MyDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you are simply starting up the restore on a new server for the very first time or overwriting the same database again, then you will only need a typical restore:

use master
go

RESTORE DATABASE [RestoreDirectlyDBfromBackup] FROM 
DISK = N'DriveName:\SQLBackups\BackupFile.BAK'
-- N'\\NetworkServerExample\SQLBackups\DBNAME_db_2009date.BAK'
WITH  FILE = 1,  MOVE N'DatabaseName_data' TO N'DriveName:\SQLData\DBName.mdf',  MOVE N'DatabaseName_log'
-- NB. if you have multiple data files you will need to do a MOVE X To FileLocation for each one of them
TO N'DriveName:\SQLLogs\DBName.ldf',  NOUNLOAD,  STATS = 10

Happy Restoring 🙂