Avoid Database Restore Issues (Errors 3219 or 3514) by a Simple Script Edit - the DB Restore Work-around

Starting with the 1000 foot view: 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. Developers especially are familiar with this process, since they are often handed off a database backup and told to simply 'deal with it', however they often find themselves in this frustrating situation where Error 3154 or 3219 comes up, and this post is to explain the simple work around, instead of reading a big thread on MSDN. Straight to the point, there is a quick method to avoid Error 3154: The backup set holds a backup of a database other than the existing database message and 3219 which are related errors to restoring. 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.

Isabella Island, Galapagos Islands, Ecuador (from a viewpoint above Targus Cove, just south the equatoral line )

Here's a typical Restore script run in SQL Server 2012/2008/5 - which will fail because the media set is not matching:
RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' F
ROM 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 (Or just create a new one, if this is not to be re-occuring).  Notice, to fix the script, 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' F
ROM 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 masterGO
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 IMMEDIATERESTORE 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 (that is not from a totally different media set, nor moves log and data files around), then you will only need a typical restore.
use mastergo

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 themTO N'DriveName:\SQLLogs\DBName.ldf',  NOUNLOAD,  STATS = 10

Happy Restoring :)