When you need to transfer Microsoft SQL Server database from one PC to another, You usually create its backup file(.bak) and restore it on the other PC.While restoring the sql server database you might get an error like this:
Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Here is the screenshot of the error:
Here is the simple solution to get rid this error in order to restore the sql server database successfully.
- Problem to this solution involves changes in the registry values.Create a Restore Point first so that you can restore your system registry if anything goes wrong.
- For creating a system restore point, go to Control Panel>Recovery>Configure System Restore.In the System Protection tab, select Create. Name the restore point something like sql server so you can remember it.
- After successful creation of the restore point, open the Run program(Win Key + R) and type regedit and click Ok. You will be prompted .Simply Click Yes.
- In the registry Editor window ,Navigate to HKEY_LOCAL_MACHINE>SOFTWARE>Microsoft>Microsoft SQL Server>MSSQLServer
- In the right hand side window, right click and select New.Select String Value and name it as BackupDirectory. Right Click on it and select Modify.In the value field type C:MyBckDir .Next Go to Your C drive and create a new folder with name MyBckDir .
- If you have chosen default instance of sql server at the time of installation,then this should work.Just restart the SQL Server Management Studio and try to restore the database.However if you have more than one instances of SQL Server, You have to add String Value key pair to the instance you are using.
- In my case, just above the MSSQLServer, there is one more folder with name MSSQL11.SQLEXPRESS .This name can differ depending on your installation.But You will find all instances of SQL Server in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server.
- Expand the folder MSSQL 11.SQLEXPRESS(name can differ in your case), and select MSSQLServer.If you can see a few value pairs on the right side, you are in right folder.Again right click in the right side window and Create a new String Value field with name as BackupDirectory and value as C:MyBckDir as you created previously in the MSSQLServer Folder.
- Make sure you have created a folder with name MyBckDir in C drive.Now try to restore the database.If you created the value key pair in the right place,the error should not appear this time and you will be able to restore the database successfully.
- If you make some wrong changes in the registry ,don’t worry just restore your system to the point you created at the first step.
- Still facing problems,let me know by commenting below and i will try to help you out.
..Or you can fill in the server property database settings for the Backup location. That also works and does not require any registry hacking.
I think the article is very helpful for people,it has solved my problem,thanks!