Home / server / Questions / 229952
Accepted
Matt Dawdy
Asked: 2011-02-02 19:27:29 +0800 CST

Error restoring database from backup

  • 772

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.

sql-server database-restore

1 Answers

  1. 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

    • 5