Thursday, October 08, 2009

Restore failed for Server 'my-pc\sqlexpress'

Recently I have upgraded my laptop from 32 bit to 64 bit OS. Most of the thing work good except restoring of Database from my old laptop.


Following is Error I was receiving
===================================
Restore failed for Server 'my-pc\sqlexpress'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()
===================================

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dailyfreecode_DATA.mdf" failed with the operating system error 5(Access is denied.).

(Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

Cause of Error
When you switch from 32 bit to 64 bit, mostly your "Program Files" folder would get changed to "Program Files (x86)" so it is obvious that path of database on old pc doesn't match on new PC and so you would get access denied error. Error is specific to incorrect path while restoring.

Note: Incase if you are creating New database, it is very less likely that you would receive this error.


Solution
Change the path of following two files to appropriate path, while restoring.
1. dbname.mdf file
2. dbname_log.ldf file

More details
Generally you would find that your database files resides in following folder (i.e. Above 2 files)
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

But with new change i found that on my PC it was trying to store on following path
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Data

So after changing path appropriately i found that i could able to restore my database.

2 comments:

Guru said...

Nice article (in simple and easy step). it solved my issue.
Keep it up DotNetGuts...

Unknown said...

. wow
. thanks dude
. its work

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape