19

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)

Improve this question
5
  • I am using SSMS to restore it.
    – DotnetSparrow
    Commented Feb 19, 2013 at 9:21
  • I am using SSMS for taking backup and same for restoring backup
    – DotnetSparrow
    Commented Feb 19, 2013 at 9:22
  • 1
    So are you just simply restoring, or are you setting specific parameters / values? It looks like the files you want to restore to already exist and are in use on your system - try defining new file names for your restore
    – marc_s
    Commented Feb 19, 2013 at 9:22
  • I put backup file with different name in sql server backup folder and trying to restore but same error.
    – DotnetSparrow
    Commented Feb 19, 2013 at 9:26
  • Looks like a database is already using those files. Have you tried using the "replace" restore option? Or as it says try the move option. These should be settable through SMO, are definitely settable through SQL, and I think through the SSMS dialog.
    – muhmud
    Commented Feb 19, 2013 at 9:44

7 Answers 7

Reset to default
28

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

Improve this answer
6
  • 1
    I've made sure of all your suggestions to be right and I still get the error.
    – Paul-Sebastian Manole
    Commented Oct 21, 2013 at 8:23
  • 12
    I figured out my error: I was using same file names for Data and Objects files. So anyone else having the same problem, make sure your file names are valid, new and are not identical for the different files your have to restore (like Data, Objects or Log).
    – Paul-Sebastian Manole
    Commented Oct 21, 2013 at 8:32
  • 1
    A little tip I just found. When you change the "Restore As" file names, it doesn't append the .mdf/.ldf extension so I was effectively trying to set them to same file. Specifying the extension cleared it.
    – Gavin Ward
    Commented Sep 3, 2014 at 10:35
  • For SQL Server 2008 R2 open the 'Options' and for File Name 'ARCHIVE_XX' Select Restore As change to different filename (.mdf).
    – skstar
    Commented Nov 11, 2014 at 9:31
  • None of the above or below answers worked for me, but i was able to get around this problem by creating new folders within my exisitng data and log folders, so they were completely separate from all other data and log files...very strange!
    – B.M.
    Commented Nov 10, 2016 at 13:55
7

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.

Improve this answer
1
  • 1
    This did the trick for me. generating the restore script and editing the duplicated files. thank you.
    – Slime recipe
    Commented Dec 9, 2018 at 14:58
3

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

Improve this answer
1

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

Improve this answer
0

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

Improve this answer
0

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.

Improve this answer
0

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.