Sql-server – Error restoring database from backup

database-restoresql server

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.

Best Answer

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