Microsoft SQL Server Backup and Restore Database RESTORE Database with REPLACE
Example
When you try to restore database from another server you might get the following error:
Error 3154: The backup set holds a backup of a database other than the existing database.
In that case you should use WITH REPLACE option to replace database with the database from backup:
RESTORE DATABASE WWIDW
FROM DISK = 'C:\Backup\WideWorldImportersDW-Full.bak'
WITH REPLACE
Even in this case you might get the errors saying that files cannot be located on some path:
Msg 3156, Level 16, State 3, Line 1 File 'WWI_Primary' cannot be restored to 'D:\Data\WideWorldImportersDW.mdf'. Use WITH MOVE to identify a valid location for the file.
This error happens probably because your files were not placed on the same folder path that exist on new server. In that case you should move individual database files to new location:
RESTORE DATABASE WWIDW
FROM DISK = 'C:\Backup\WideWorldImportersDW-Full.bak'
WITH REPLACE,
MOVE 'WWI_Primary' to 'C:\Data\WideWorldImportersDW.mdf',
MOVE 'WWI_UserData' to 'C:\Data\WideWorldImportersDW_UserData.ndf',
MOVE 'WWI_Log' to 'C:\Data\WideWorldImportersDW.ldf',
MOVE 'WWIDW_InMemory_Data_1' to 'C:\Data\WideWorldImportersDW_InMemory_Data_1'
With this statement you can replace database with all database files moved to new location.
