4

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.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
Matt Dawdy
  • 429
  • 1
  • 9
  • 19
  • 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 02 '11 at 03: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 02 '11 at 04:19

1 Answers1

5

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

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259