Restore Database – Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’

-> I was trying to restore a database using SQL Server management studio. Right Click “Database” Folder -> Restore Database -> select “Device” -> Navigate the backup file and Click “OK”.

-> Received below error,

TITLE: Microsoft SQL Server Management Studio
——————————
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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=BackupDirectory&LinkId=20476
——————————
BUTTONS:
OK
——————————

Error

-> The error states that Property BackupDirectory is not available.

-> Right Click “SQL Server Instance” -> Click properties -> Database Settings -> Check “Database Default Locations”.

-> In my case “Backup: ” was empty,

Settings.PNG

-> Changed the default backup location to a valid path and tried the restore again. It worked fine this time!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

Advertisements

Logshipping metadata table log_shipping_monitor_secondary is not getting updated

-> I worked on a datacentre migration recently. One of the server involved in the migration was a Logshipping Primary and Monitor server. Logshipping primary and Monitor server were in new datacentre, but the logshipping secondary was at old datacentre.

-> After the migration, all of the logshipping jobs like Backup, copy and restore were working as expected. But the LS_Alert job was failing with below error,

Executed as user: <LOGIN>. The log shipping primary database <ServerName>.<DatabaseName> has backup threshold of 45 minutes and has not performed a backup log operation for 320 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed.

-> I checked the metadata tables msdb..log_shipping_monitor_primary and msdb..log_shipping_monitor_secondary to get more details.

-> Metadata table msdb..log_shipping_monitor_primary was updated recently. But msdb..log_shipping_monitor_secondary table was not updated recently. The time it was last updated was the time when we moved the Logshipping Primary and monitor server to new datacentre.

-> I started a profiler trace on the primary and monitor server without much luck.

-> I then started a profiler trace on the secondary server and found below messages,

OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.

-> I tried connecting the monitor server using SQL Server management studio from secondary server without much luck. I tried pinging the monitor server from secondary server and got the below,

C:\Users>ping <MonitorServer>
Pinging <MonitorServer> [192.152.0.3] with 32 bytes of data:
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.

Ping statistics for 192.152.0.3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

-> Updated my network team and they corrected it. Tried connecting the monitor server using SQL Server management studio from secondary server and this time it worked.

-> Metadata table msdb..log_shipping_monitor_secondary started getting updated normally.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

Using certificates with SQL server

-> Install SDK from https://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx to get makecert.exe.

-> Makecert.exe will be present in location C:\Program Files (x86)\Windows Kits\8.1\bin\x64\ for X64 and C:\Program Files\Windows Kits\8.1\bin\x86\ for X86.

-> When using makecert.exe, make sure you use the correct server FQDN. Any mistakes in the FQDN while creating the certificate will result in the certificate not being used by SQL server.

-> Run the below query from command prompt.
makecert -r -pe -n “CN=<Server_Name_FQDN>” -b 02/13/2017 -e 12/01/2040 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine  -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12

-> Click on Run -> MMC -> File -> Add/remove snap in -> Click on Certificates -> Add it.

-> Select “Computer account” -> Finish.

-> Select Personal -> Certificates -> Look at the certificate you just created -> Double click it.

-> Click on Details -> Go to thumbprint. You will see something like below,
‎e0 fd 68 2a af 72 74 fb 24 24 ea 77 99 60 85 58 e1 61 79 1e

-> Remove the spaces and place it in a notepad session.

-> Open registry and check the certificate key that resides in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.<InstanceName>\MSSQLServer\SuperSocketNetLib and see if the values in “certificate key” is same as the one placed in the notepad session. If the “certificate” key in the registry is blank or it has a different value, then copy the thumbprint value from the notepad session and put it in the “Certificate” key in registry.

-> Open the SQL server configuration manager and get to certificate tab as shown below. For a Standalone SQL server instance, if everything is fine with the certificate configuration. You should see it in the certificate as shown below.

blog11_5

-> In case if it is a clustered SQL server instance, you wont see the certificate in the configuration manager as the certificate will be created using virtual host name and not with the physical node name.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.