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

Archival Report – Getting before and after row count post data archival

-> Data archival on a SQL Server database is a common activity that needs to be performed by a SQL Server database administrator.

-> A report that contains before and after row count post Data Archival with the row count difference will be of much help.

-> Download  Archival_Report_Job.sql and execute the query using SQL Server Management Studio on database server where Archiving will be performed.  Remember to change the database name to appropriate database where Archiving will be performed in the downloaded script. This will create a job called Archival_Report and the job can be found within the jobs folder under SQL Server Agent. This will be an one time activity and should be performed on database server during initial setup.

-> The first step on job Archival_Report inserts data related to table size into table Archival_Table_Details.

-> The second step on job Archival_Report deletes data older than 7 years from table Archival_Table_Details.

-> SQL Server agent job “Archival_Report” should be executed before the start of data archival either manually or by executing below code from application scheduler agent,

sqlcmd -SSQLServerInstance -E -Q"Exec msdb..sp_start_job N'Archival_Report'"

-> Wait for data archival to complete. Once the Data Archival is complete. SQL Server agent job “Archival_Report” should be executed once again either manually or by executing below code from application scheduler agent,

sqlcmd -SSQLServerInstance -E -Q"Exec msdb..sp_start_job N'Archival_Report'"

-> Execute below query on the context of Archived database on database server where Archiving was performed. Remember to change the database name to appropriate database where Archiving will be performed in the below script. Archival report will be displayed in the output tab as part of query window in SQL Server Management Studio.


use [JBDB]
GO
;with Archival_Report_CTE as (
select TableName, [Time],[# Records],[Table_used_Space GB],row_number() Over(Partition by TableName order by Time DESC ) RowNumber
from Archival_Table_Details)

Select
Max(Case when RowNumber=1 then Cast([Time] as date) else null End) Time,
TableName
,Max(Case when RowNumber=2 then [# Records] else null End) [Before # Records]
,Max(Case when RowNumber=2 then [Table_used_Space GB] else null End) [Before_Table_used_Space GB]
, Max(Case when RowNumber=1 then [# Records] else null End) [After # Records]
, Max(Case when RowNumber=1 then [Table_used_Space GB] else null End) [After_Table_used_Space GB]
,Max(Case when RowNumber=2 then [# Records] else null End)-Max(Case when RowNumber=1 then [# Records] else null End) [# Records Difference]
From Archival_Report_CTE
where RowNumber<=2 and TableName NOT LIKE '%Archival_Table_Details%'
Group by TableName

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.

Finding Transaction latency in an Always on Synchronous replica

1) Execute below query on your Always on Primary replica,


-- Create required objects to collect the data
use [Tempdb]
GO
IF OBJECT_ID('Perfcounters') IS NOT NULL
DROP TABLE Perfcounters
GO
CREATE TABLE Perfcounters
(CapturedTime DATETIME NOT NULL,
[Transaction Delay] BIGINT, [Mirrored Write Transactions/sec] BIGINT
)
GO

ALTER TABLE [dbo].Perfcounters
ADD [Transaction Delay in MS]
AS (convert(decimal(5,2),[Transaction Delay]*1.0/[Mirrored Write Transactions/sec]*1.0))
PERSISTED NOT NULL

2) Below query can be executed in a query window or as a SQL Server agent job in primary replica. Remember to change “Instance_Name=’Alwayson Database Name'” to appropriate Alwayson database name before executing the query,


use [Tempdb]
GO
set nocount on
while (1=1)
begin
insert into Perfcounters
SELECT CURRENT_TIMESTAMP as Time
,pt.* FROM(SELECT RTRIM(object_name) + ' : ' + counter_name CounterName
, cntr_value from sys.dm_os_performance_counters
where Instance_Name='Alwayson Database Name' and counter_name in ('Transaction Delay','Mirrored Write Transactions/sec')) as SourceData
PIVOT
(SUM(cntr_value)
FOR CounterName
IN ([SQLServer:Database Replica : Transaction Delay],[SQLServer:Database Replica : Mirrored Write Transactions/sec])
) pt
waitfor delay '00:00:03'  -- Change this as per your requirement for collection
End

3) While the above query is getting executed from your query window or SQL Server agent job, you can execute the below query to get the latency values,


use [Tempdb]
GO
select * from Perfcounters order by CapturedTime desc

4) Remember to stop the query running as part of step 2 in this article on query window or as a SQL Agent job when not required.

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.