Microsoft SQL Server Backup and Restore Database RESTORE Database with REPLACE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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.



Got any Microsoft SQL Server Question?