Scribd
Upload
18 views2 pages

Usingdbatools

The document outlines a PowerShell script that connects to multiple SQL Server instances to retrieve and update server details in a central database. It discovers registered SQL servers, queries their version and other properties, and uses a MERGE statement to insert or update this information in a specified table. The script also includes error handling for connection issues and displays messages about the operations performed.

Uploaded by

Pamela Rose Papong Capangpangan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
18 views2 pages

Usingdbatools

The document outlines a PowerShell script that connects to multiple SQL Server instances to retrieve and update server details in a central database. It discovers registered SQL servers, queries their version and other properties, and uses a MERGE statement to insert or update this information in a specified table. The script also includes error handling for connection issues and displays messages about the operations performed.

Uploaded by

Pamela Rose Papong Capangpangan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Set-DbatoolsInsecureConnection -SessionOnly

# Database configuration
$centralServer = "DESKTOP-SFQAKOI\SQLTESTSRV1" # Replace with your central
SQL Server instance name
$centralDatabase = "DBA" # Central database name
$centralTable = "ServerVersions" # Table name for storing server details

# Discover available SQL servers


$sqlServers = Get-DbaRegisteredServer

# Output the discovered servers


$sqlServers | ForEach-Objects { Write-Host "Discovered server: $($_.ServerName)" }

# Loop through each discovered server and query its SQL information
#foreach ($server in $sqlServer) {
try {
Write-Host "Connecting to $($server.ServerName) to retrieve SQL Server
details..."

# Define the query to get version and server info


$query = @"
SELECT
SERVERPROPERTY('MachineName') AS ServerName,
SERVERPROPERTY('DomainName') AS DomainName, -- Retrieves the domain
name
SERVERPROPERTY('ProductVersion') AS Version,
SERVERPROPERTY('ProductLevel') AS PatchLevel,
SERVERPROPERTY('Edition') AS Edition,
GETDATE() AS DateChecked
"@

# Execute the query on the server


$result = Invoke-DbaQuery -SqlInstance $server.ServerName -Query $query

# Process the result if it is not empty


if ($result) {
$serverName = $result.ServerName
$domainName = $result.DomainName
$version = $result.Version
$patchLevel = $result.PatchLevel
$edition = $result.Edition
$dateChecked = $result.DateChecked

Write-Host "Server: $serverName, Domain: $domainName, Version:


$version, Patch Level: $patchLevel, Edition: $edition"

# Insert or update server information using MERGE (Upsert)


$upsertQuery = @"
MERGE INTO $centralDatabase.dbo.$centralTable AS target
USING (VALUES ('$serverName', '$domainName', '$version', '$patchLevel', '$edition',
'$dateChecked')) AS source (ServerName, DomainName, Version, PatchLevel, Edition,
DateChecked)
ON target.ServerName = source.ServerName
WHEN MATCHED THEN
UPDATE SET
DomainName = source.DomainName,
Version = source.Version,
PatchLevel = source.PatchLevel,
Edition = source.Edition,
DateChecked = source.DateChecked
WHEN NOT MATCHED THEN
INSERT (ServerName, DomainName, Version, PatchLevel, Edition, DateChecked)
VALUES (source.ServerName, source.DomainName, source.Version,
source.PatchLevel, source.Edition, source.DateChecked);
"@

# Execute the upsert query using dbatools


Invoke-DbaQuery -SqlInstance $centralServer -Database $centralDatabase
-Query $upsertQuery
Write-Host "Inserted/Updated information for $serverName"
} else {
Write-Warning "No data returned for $($server.ServerName)"
}
} catch {
Write-Warning "Failed to connect to or query $($server.ServerName). Error:
$_"
}
}

Write-Host "SQL Server information has been updated in the database


$centralDatabase in the table $centralTable."

You might also like