I break a lot of things on my SQL environment and I believe that is a very good way to learn things. Today, I wanted to perform a restore of a database using a backup which I took earlier. In this blog post, let us learn how I fixed Property BackupDirectory is Not Available for Settings.
As soon as I clicked database, I was welcomed with the below error message.
Here is the text of the message.
Event ID: 7011
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)
I was not sure from where I would get BackupDirectory property so I capture profiler trace while clicking on restore option and found below when I searched for BackupDirectory
declare @BackupDirectory nvarchar(512) if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0) select @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512)) else exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
After debugging further, it was easy to find that its looking for a registry key for my SQL instance and then I recalled that I was playing with a registry setting for one of my previous blog posts.
I found that below setting was empty.
As soon as I entered a value in the highlighted text box of “backup” and hit OK, I was able to get to the restore UI without any error.
Have you ever used default setting for backup? Do you know the use of it?
Reference: Pinal Dave (https://blog.sqlauthority.com)