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

Katung M Aduwak 21 Reputation points
2022-10-03T22:06:26.047+00:00

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

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,451 Reputation points
    2022-10-04T05:11:32.323+00:00

    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.

    0 comments No comments