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:

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.

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.

usemasterGO

declare @backupSetId asint

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

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

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