Sql Server 2008R2 SMO Restore Error

Question

I have SQL Server 2008 installed locally on my computer. Using C# + SMO i'm trying to do a restore of the server however I keep getting this error

File 'AdventureWorksLT2008R2_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008R2_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008R2_Log.ldf' cannot be overwritten.  It is being used by database 'AdventureWorksLT2008R2'.
File 'AdventureWorksLT2008R2_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008R2_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

How do I get around this issue? 

Answer 1

The error tells you that you need the MOVE to define a new location.  Sample code:

RESTORE DATABASE [NewCopy_YourDatabase] 
FROM  DISK ='\\Backup\YourDatabase\YourDatabase_FULL_20120318.bak' WITH  FILE = 1,  
RECOVERY,  NOUNLOAD,  REPLACE, STATS = 10 , 
move 'YourDatabase_Data' to 'D:\Data\NewCopy_YourDatabase_Data.MDF' , 
move 'YourDatabase_Log' to 'F:\Logs\NewCopy_YourDatabase_Log.LDF'

As you can see, through the MOVE operator you can define a new file name to hold the data for this database.   Because YourDatabase_Data.MDF and YourDatabase_Log.LDF already exist for YourDatabase, the NewCopy_YourDatabase needs new file names.

Make sense?
RLF

PS - Now it is up to you to change the SMO process to provide new file names.  Or just script out the restore command from SSMS and make the needed changes.

Answer 2

Added these two lines of code to correct the problem. Thanks for the help!

RelocateFile rsFile = new RelocateFile(@"AdventureWorksLT2008R2_Data", @"C:\Program Files\MicrosoftSQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdvenRes.mdf");

RelocateFile rsLog = new RelocateFile(@"AdventureWorksLT2008R2_Log", @"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdvenRes.ldf");

            

Answer 3

It can be a critical situation of sql server corruption which user can overcome using some DBCC command but still if they don't work then try for some management utility like sql recovery tool.

I can suggest you a page which will surely help you, http://repairmdffile.devhub.com/