CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DotnetSparrow
DotnetSparrow

Reputation: 27996

database restore failing with move

I am trying to restore a database backup but getting error:

Restore failed for Server 'ASIF-VAIO'. (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\uwa.mdf' is claimed by 'Aston_Fresh_log'(2) and 'Aston_Fresh'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

Upvotes: 20

Views: 48103

Answers (8)

Vinz
Vinz

Reputation: 3208

If the restore with conflicting file paths doesn't work via the SSMS dialogs, one might have to do the following via T-SQL:

1. Identify logical file names in the backup

RESTORE FILELISTONLY 
FROM DISK = 'C:\backupfile.bak';

This will list the files included in the backup with properties such as their logical name and physical path that they were backed up from.

2. Restore with new physical file locations

For each logical file listed in step 1, manually specify the target file location with the MOVE option:

RESTORE DATABASE mydatabase
FROM DISK = 'C:\backupfile.bak'
WITH
    MOVE 'LogicalDataFileName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LogicalDataFileName.mdf',
    MOVE 'LogicalLogFileName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LogicalDataFileName.ldf'

Make sure none of these new target locations are the same and that all files listed from step 1 are specified with the MOVE option.

NOTE: This example assumes an SQL Server Instance installation in C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER (MSSQL16 for SQL Server 2022). These need to be changed according to the actual SQL Server environment.

Upvotes: 0

tinker
tinker

Reputation: 662

In my case, the database has 2 mdf files. And the error came, because I'm trying to restore both mdf files as the same name.

Just rename the second mdf file, under "Restore As".

enter image description here

Upvotes: 1

Kellen Stuart
Kellen Stuart

Reputation: 8953

In my case, there was already a .mdf and .ldf file in my \DATA folder, so I had to create two new files:

New-Item C:\path\to\sql\DATA\NewDatabase.mdf
New-Item C:\path\to\sql\DATA\NewDatabase_log.ldf

And then in the SQL manager you need to select those new files when restoring the database.

Upvotes: 0

Dispersia
Dispersia

Reputation: 1438

If you have this issue and it's not the above, try under the Restore Options > Files, check the Relocate all files to folder checkbox.

enter image description here

Upvotes: 0

Karolina Ochlik
Karolina Ochlik

Reputation: 938

I know it's long since the last answer, but I happened to search in google for solution for this problem. What did it for me, was scripting the restore (changing file name did not do the trick) and manually changing the filenames in code

RESTORE DATABASE [DB_NAME] 
FILE = N'[name]',  
FILE = N'[name1]',
FILE = N'[name2]' 
FROM  DISK = N'[file_path]' 
WITH  FILE = 1m
MOVE N'[name]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\\[name].mdf',
MOVE N'[name1]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\\[name1].mdf',
MOVE N'[name2]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\\[name2].mdf',
MOVE N'[logname]' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\\[logname].ldf'
NOUNLOAD,
REPLACE,
STATS = 10
GO

Regards

Upvotes: 2

TheRealKernel
TheRealKernel

Reputation: 351

This post has some excellent answers but I don't believe my solution was covered here, or I didn't understand the answer/comment.

However, when I encountered this error I was restoring a database with 2 indexes (Primary and Index). The issue was that when restoring it had created two .ndf files, one for each index, but had named them the same thing.

So essentially I had two "Restore As" files restoring to "D:\MSSQLDATA\DatabaseName.ndf.

To overcome this I had to change one of the file names, so for example I changed

 Index      |    D:\MSSQLDATA\DatabaseName.ndf
 Primary    |    D:\MSSQLDATA\DatabaseName1.ndf

having unique file names fixed this for me.

Upvotes: 7

Lisa
Lisa

Reputation: 31

This worked for me : giving a different name to each MDF and LDF file in the script section.

MOVE N'R_Data' 
  TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db1.mdf',

MOVE N'R_audit' 
  TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Build51_Testing_db2.mdf',

etc...

Originally suggested by Alberto Morillo

Upvotes: 3

marc_s
marc_s

Reputation: 755431

When restoring, you need to be sure to

  • pick a new database name that doesn't already exist (unless you want to overwrite that pre-existing database)

enter image description here

  • you tick the Overwrite option in the Options tab page and define valid and new file names for the .mdf and .ldf file so that you don't accidentally overwrite another database on your system:

enter image description here

Upvotes: 28

Related Questions