SQL Server restore database – the backup set holds a backup of a database other than the existing database

I couldn’t use SQL Server Management Studio and restore database because it was crashing for some reason.
So, I had to use query

I’ve found helpful code on Stack Overflow and tried to use it.
Nothing was special about it until I got error ‘The backup set holds a backup of a database other than the existing ‘xx’ database.’

The solution was to use WITH REPLACE clause. For example

USE Master

RESTORE FILELISTONLY FROM DISK = 'C:\sqldata_backups\MyDB.bak'
RESTORE DATABASE LEOImport FROM DISK = 'C:\sqldata_backups\MyDB.bak'
WITH 
	MOVE 'MyDB' TO 'C:\sqldata\MyDB.mdf',
	MOVE 'MyDB_log' TO 'C:\sqldata\MyDB_log.ldf',
	REPLACE

To get logical names and locations of the files use following query

SELECT name, physical_name AS current_file_location
FROM sys.master_files 

Leave a Reply

Your email address will not be published. Required fields are marked *