Get database properties with Powershell

Standard

Based on the previous article “Get Database and database file properties” I wanted to get these values from multiple servers with the results in an single and a combined comma separated file.
That why I created a script to get the database properties with Powershell.

The script below takes two arguments:

  1. input: full path to input file with SQL server instance(s)
  2. output: full path to directory to output files to

Make sure that the output directory doesn’t end with a “\”!

The format for the input file is easy. Just enter a SQL Server instance with the full name. For a default instance use the name of the servers. For a named instance use the servername plus the instance name i.e. “dbserver1\inst1”.
Make sure that every servers is on a new line.

The script outputs a file per server and a file with all the results combined.

param

(
[string]$i = $(throw "You must specify an input file")
, [string]$o = $(throw "You must specify an output directory")
)

CLS

if((Test-Path $i) -and (Test-Path $o))
{
# Load needed assemblies
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerCmdletSnapin100
}
if ( (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PSSnapin SqlServerProviderSnapin100
}

# Get the servers from the list
$servers = Get-Content $i

$query = "
SELECT
@@SERVERNAME AS 'server'
, d.name AS 'database name'
, d.compatibility_level AS 'compatibility level'
, d.collation_name AS 'collation'
, d.state_desc AS 'state'
, f.physical_name AS 'file location'
, d.recovery_model_desc AS 'recovery model'
, 'file type' =
(
CASE f.type_desc
WHEN 'ROWS' THEN 'DATA'
ELSE type_desc
END
)
, 'size mb' = (size/128)
, 'max size mb' =
(
CASE CONVERT(VARCHAR(20), f.max_size)
WHEN -1 THEN CONVERT(VARCHAR(20), 'Unlimited')
ELSE CONVERT(VARCHAR(20), (f.max_size/128))
END
)
, 'growth' =
(
CASE CONVERT(VARCHAR(20), f.is_percent_growth)
WHEN 1 THEN CONVERT(VARCHAR(20), f.growth) + ' Percent'
--ELSE CONVERT(VARCHAR(20), ((f.growth * 8) / 1024)) + ' MB'
ELSE CONVERT(VARCHAR(20), (f.growth / 128)) + ' MB'
END
)

FROM
sys.databases d
INNER JOIN sys.master_files f
ON d.database_id = f.database_id
WHERE
d.name NOT IN ('Master','Model','Msdb','TempDB')
"

$exportFileAll = $o + '\DatabaseProperties_All.csv'

$results = $null
$resultsAll = $null

foreach($server in $servers)
{
$exportFile = $o + '\DatabaseProperties_' + $server + '.csv'

Write-Host "Exporting database properties for server $server" -ForegroundColor Green

try
{
$results = Invoke-Sqlcmd -ServerInstance $server -Database "master" -Query $query -ErrorAction Stop | Out-Null

if($results -ne $null)
{
$resultsAll += $results
$results | Export-Csv -Path $exportFile -NoTypeInformation -Delimiter ";"
}
else
{
Write-Host "No records retrieved from server: " $server -ForegroundColor Gray
}
}
catch
{
Write-Host "Error connecting to server: " $server -ForegroundColor Red
}

}

if($resultsAll -ne $null)
{
$resultsAll | Export-Csv -Path $exportFileAll -NoTypeInformation -Delimiter ";"
}
else
{
Write-Host "No records retrieved!" -ForegroundColor Gray
}
}
elseif(Test-Path $i)
{
Write-Host "Could not find input file, please check the given path!" -ForegroundColor Red
}
elseif(Test-Path $o)
{
Write-Host "Could not find output directory, please check the given path!" -ForegroundColor Red
}

If you like the script please leave a comment

Leave a Reply

Your email address will not be published.