Friday, March 23, 2012 5:17 PM
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?
Friday, March 23, 2012 6:00 PM
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?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.
Friday, March 23, 2012 9:40 PM
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");
Monday, June 04, 2012 6:08 PM
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/