Can anyone answer why my restore operation is giving me the attached error ?

Question

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore of database 'AdventureWorks2019' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file 'AdventureWorks2017' cannot be moved by this RESTORE operation. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Answer 1

Hi @Katung M Aduwak ,

This error seems to occur because you selected a new location when you restored the full backup, then when you did the differential recovery you did not change it to the same physical location.
If you're sick of being at the mercy of SSMS then learn to do the restore in T-SQL:

-- use this command to get the logical names for the restore   
-- so you can specify a new location using MOVE  
RESTORE FILELISTONLY FROM DISK='c:\av_full.bak'  
  
-- restore the full backup, don't recover yet  
RESTORE DATABASE adventureworks1 FROM DISK= 'c:\av_full.bak' WITH   
MOVE 'AdventureWorks_Data'  to 'C:\AdventureWorks_Data.mdf',  
MOVE 'AdventureWorks_Log'   to 'C:\AdventureWorks_Log.ldf' ,   
NORECOVERY  
  
-- restore the differential  
RESTORE DATABASE adventureworks1 FROM DISK= 'c:\av_diff.bak'  

Please refer to this similar thread and this blog.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.