Today, while working with SQL Server, I was trying to restore a DB from .bak file and got a strange error while restoring using the normal procedure. I was working on SQL Server 2008 R2 and the backup was from an old version making me think the problem may be because of incorrect SQL versions. The error read as below:
TITLE:
Microsoft SQL Server Management Studio
——————————
Restore failed for Server ‘MYSQLSERVER’. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+
((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.
FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError:
File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf’
is claimed by ‘MyDatabase_log’(2) and ‘MyDatabase’(1).
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.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476.
As usual, the links provided by SQL Server were of least use. After reading the error closely a few times, I understood the error is not related to SQL Server version but is related to the files being used by the restoration. It was found that the .mdf file of the database is being used twice by the restoration process and hence tried to restore the database using a script. Below is the solution to restore the database using a script.
RESTORE DATABASE [MyDatabase]
FROM DISK = N'E:\MyDatabase.bak'
WITH FILE = 1,
MOVE N'MyDatabase'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyDatabase.mdf',
MOVE N'MyDatabase_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MyDatabase.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
Using the script and changing proper values for your database, you will be able to restore the database with ease. Hope you like this post!
A passionate developer with over 10 years of experience and building my software company code by code. Experience withMS Technologies like .Net | MVC | Xamarin | Sharepoint | MS Project Server and PhP along with open source CMS Systems like Wordpress/DotNetNuke etc.
Love to debug problems and solve them. I love writing articles on my website in my spare time. Please visit my Website for more details and subscribe to get technology related tips/tricks. #SOreadytohelp