Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got a backup of my SQL Server 2005 database that I want to put on my dev machine (much quicker to develop against than via writing queries through remote desktop.)

I was able to restore this on one machine at work, so I know that the backup is good (well, I assume it is -- I did have to copy it to an external harddrive and then copy it off again onto my current machine).

The error I'm getting is this:

TITLE: Microsoft SQL Server Management Studio


Restore failed for Server 'XPS17200911\SQL2005'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476


ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: File 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.ndf' is claimed by 'BankA_Tables02'(4) and 'BankA_Tables01'(3). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&LinkId=20476

And the SQL Script that is generated (you know what I mean) is this:

RESTORE DATABASE [TDC] FROM  DISK = N'C:\Users\Matt\Desktop\banka.bak' WITH  FILE = 1,  
MOVE N'BankA' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.mdf',  
MOVE N'BankA_Tables01' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.ndf',  
MOVE N'BankA_Tables02' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.ndf',  
MOVE N'BankA_Tables03' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.ndf',  
MOVE N'BankA_Log' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TDC.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

I formatted it a little bit, but only with carriage returns.

I tried to Google this sucker, but the specific file names might be fouling it up. Plus all of the results seemed to assume a simple database format (this one has 5 files, not just 2 like "normal" SQL Server databases) and all dealt with changing filenames, and I don't think that's my problem here.

Any help would be greatly appreciated.

share|improve this question
1  
Okay, I'm an idiot. If someone wants to claim a quick answer, say "Hey Dumbass, it's trying to restore 3 different files all to the same filename. Change the 3 TDC.ndf names to TDC_01.ndf, TDC_02.ndf, and TDC_03.ndf in the 3 middle statements." And I'll give you an accepted answer. –  Matt Dawdy Feb 2 '11 at 3:34
    
I made a CW answer for you to check below (I won't get any rep from it), but just so you know you are more than welcome to answer your own question and mark it as correct. If you want to do that, I'll delete my "answer" –  Mark Henderson Feb 2 '11 at 4:19

1 Answer 1

up vote 4 down vote accepted

It's trying to restore 3 different files all to the same filename.

Change the 3 TDC.ndf names to TDC_01.ndf, TDC_02.ndf, and TDC_03.ndf in the 3 middle statements

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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