SQL SERVER – Error: Property BackupDirectory is Not Available for Settings

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.

Solution

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?

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.