I mentioned in a previous post that I was going to try and give up my beloved VBScript and work in some more PowerShell. I’ve been thinking for a while I would make a table to store some information about all the databases that exist across our SQL Servers. I thought about writing it in VBScript, TSQL, and .NET before settling on PowerShell. It’s a new language to learn, and while it’s powerful, there is only so much time in the day. So here it is. My first full-fledged, ready-for-primetime PowerShell script.
First, let’s talk about the script is about. We need to collect the SQL Server Name, Database Name, SQL version, Database creation date, Data File size, Log File size, and the Database Status. The easist way to do this is using the .NET classes available through SQL Server Management Objects, known as SMO.
The first part of our script loads the SMO namespace and the PowerShell SQL Server Snap-In’s. At the moment the script only uses Invoke-SQLCmd, but that could be expanded.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Next we create an empty array variable called $DBInfo. In the script I don’t use it yet, but I have played with using ConvertTo-HTML and Out-File to make a simple website. I have a table (database_info) in a database we use for DBA information (mgmtdb) on one of our servers. You would need to make a table somewhere of your liking with columns that match our INSERT statement later in the script.
$DBInfo = @()
$serverlist = invoke-sqlcmd -serverinstance "yourserver" -query "select distinct(instance_name) from mgmtdb.dbo.database_info"
Once we have our list of servers we can begin stepping through it and connecting to each one. We create the $svr variable and connect it to the current server in the loop. We then populate the $version variable since it won’t change for any database on this server. Then we step through each database that exists on the server using the $svr.databases collection.
ForEach ($server in $serverlist)
{
$svr = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server.instance_name
$version = $svr.version
foreach ($db in $svr.databases)
{
At the beginning of each loop we set the value of $dbsize and $logsize as these properties can be empty if the DB is unavailable. We then check the database status before we attempt to get the size properties. Because the $db.size property includes data and log files we take the sum of the size properties in the $db.logfiles collection and subtract it from the $db.size property. We also have to divide the log file size by 1024 because it is listed in KB not MB. Too bad there’s no datafiles collection!
$dbsize = "NULL"
$logsize = "NULL"
If ($db.status -match "Normal")
{
$dbsize = $db.size – ($db.logfiles|measure-object -property size -sum).sum / 1024
$logsize = ($db.logfiles|measure-object -property size -sum).sum / 1024
}
Now we create some variables for use in our INSERT statement. the $object.property variables didn’t seem to jive well with all the quotes, so it was easier to set up some base variables. We also set the date to a SQL datetime friendly format. And remove any commas from the $db.status property as our SQL INSERT statement won’t like those at all!
$thedate = get-date -format "yyyy-MM-dd hh:mm:ss"
$svrname = $svr.name
$dbname = $db.name
$dbcreatedate = $db.createdate
$dbstatus = ($db.status).tostring()
$dbstatus = $dbstatus.Replace(","," ")
This next section is kind of meaning less for the script, but it’s wicked fun. The properties and variables we’ve created can be hard to format for output. The general PSObject object take care of this delimma for us. First we create a new object, $obj. Then we add each of our different database properties. Once we are done we add the object to our $DBInfo array. During each loop a new object is created and added to the array. You could use "$DBInfo | Format-Table" to more easily view the information, or "$DBInfo | ConvertTo-HTML | Out-File dbinfo.html" to make a quick web page. If your already have the web server up an automated daily job could overwrite the page so that interested parties can keep track of their database sizes. We also use this to see if new databases "pop-up" because of a wiley developer!
$obj = New-Object PSObject
$obj | Add-Member NoteProperty -name ServerName -value $svr.name
$obj | Add-Member NoteProperty -name DatabaseName -value $db.name
$obj | Add-Member NoteProperty -name SQLVersion -value $version
$obj | Add-Member NoteProperty -name CreateDate -value $db.createdate
$obj | Add-Member NoteProperty -name DBSize -value $dbsize
$obj | Add-Member NoteProperty -name LogSize -value $logsize
$obj | Add-Member NoteProperty -name DBStatus -value $db.status
$DBInfo+=$obj
The final piece is to build your SQL INSERT statement, then use Invoke-SQLCmd to run it.
$sqlcmd = "INSERT INTO mgmtdb.dbo.SQL_Web_Data VALUES (‘$svrname’,’$dbname’,’$version’,’$dbcreatedate’,$dbsize,$logsize,’$dbstatus’,’$thedate’)"
invoke-sqlcmd -serverinstance "yourserver" -query $sqlcmd
}
}
I hope this has provided some new insights into PowerShell for anyone who reads this. I know writing the script (which took about 2 hours with research) taught me a ton of things I’ll use in future PowerSehll scripts, most notably the use of PSObject for formatting and data manipulation.
Have a Happy Thanksgiving!
Scott is a Geek 