How to add a Sql code to a powershell script

Question

backup_report.txtI have the below sql script to check if any failover happened on the last 24 hrs and we want to use that in the existing powershell backup report script to check that condition before it reports, the below sql code only returns a timestamp

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel',null,null,null) WHERE object_name = 'error_reported' )

SELECT Top 1 data.value ('(/event/@timestamp)[1]','datetime') AS [timestamp] FROM cte_HADR WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480

Answer 1

backup_report.txt.

Answer 2

There's no "$Query" variable in your script. There's a "$BKQuery" though. Are you just using the wrong variable name as the Invoke-SQLCommand cmdlets "-Query" parameter value?

Answer 3

Hi Sara,

Thank you for posting in the Q&A Forums.

If you want to use this SQL query in a PowerShell script, you can use the class in Invoke-Sqlcmd (requires the SQL Server PowerShell module) or the System.Data.SqlClient namespace to execute the SQL query.

Here's an example of PowerShell using Invoke-Sqlcmd:

$server = "YourServerName"

$database = "master"

$query = @"

DECLARE @AvailabilityGroupName NVARCHAR(256) = 'YourAvailabilityGroupName';

DECLARE @Threshold DATETIME = DATEADD(HOUR, -24, GETDATE());

SELECT

replica_server_name,

primary_replica,

is_local,

last_failover_time,

failover_ready

FROM

sys.dm_hadr_availability_replica_states

WHERE

group_id = (SELECT group_id FROM sys.availability_groups WHERE name = @AvailabilityGroupName)

AND last_failover_time > @Threshold

AND last_failover_time IS NOT NULL;

"@

$result = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query

if ($result -ne $null) {

Write-Host "Failover detected within the last 24 hours:"

$result | Format-Table

} else {

Write-Host "No failover detected within the last 24 hours."

}

Best regards

NeuviJ

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.