First let me say, I know my Powershell skills are sub-par. I’m working on it. Slowly but surely. That said, I had a problem to solve. It’s one that I could have done with TSQL, but it would be very ugly TSQL, probably involving dynamic queries, and even for admin scripts, I try to avoid that. So, I went for SMO and WMI wrapped by Powershell to solve the problem.
What was the problem you ask? We automate as many of our processes as we can. One process we do is resetting databases from production or other sources. Our processes work very well, but we occasionally run into a problem where the source system db has grown and the target system doesn’t have the required disk space. So, we needed a method for validating it. This is my first pass at that method. I know it needs work, but it’s functional, so I thought I’d share.
param($sourceserver,$targetserver,$databasename)
[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null$source = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “$sourceserver”
$target = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “$targetserver”$sourcedb = $source.Databases[$databasename]
$targetdb = $target.Databases[$databasename]$sourcelogfiles = $sourcedb.logfiles
$targetlogfiles = $targetdb.logfiles## walk through all the log files
foreach ($slf in $sourcelogfiles)
{
$tlf = $targetlogfiles[$slf.name]
##See if the target is smaller than the source
if ($slf.Size -gt $tlf.size)
{
##if the target is smaller, check the drive for free space
$drive = Split-Path $tlf.FileName -Qualifier
$server = $targetserver.Substring(0,$targetserver.IndexOf(“\”))
$driveinfo = gwmi win32_logicaldisk -computername $server |where-object {$_.name -like “$drive”}|select name,freespaceif ($slf.size -gt ($driveinfo.freespace + $tlf.Size))
{
Write-Output “Drive: $drive has insufficient space. $databasename Source: $slf.size, Target: $tlf.size + $driveinfo.size”
}
}
}$sourcedatagroups = $sourcedb.FileGroups
$targetdatagroups = $targetdb.FileGroups##walk through all the data files
foreach ($sdg in $sourcedatagroups)
{
foreach ($sdf in $sdg.Files)
{
$tdg = $targetdatagroups[$sdg.name]
$tdf = $tdg.Files[$sdf.name]if ($sdf.Size -gt $tdf.Size) {
$drive = Split-Path $tdf.FileName -Qualifier
$driveinfo = gwmi win32_logicaldisk -ComputerName $server | where-Object{$_.name -like “$drive”}|select name,freespace
if ($sdf.Size -gt ($driveinfo.freespace + $tlf.Size)) {
Write-Output “Drive: $drive has insufficient space. $databasename Source: $slf.size, Target: $tlf.size + $driveinfo.size”
}
}
}
}
It’s pretty straight forward. It gets a connection to each SQL instance it’s passed, goes to the database in question, which in our situation will always have the same name, and walks the log files and data files, which again, will always have the same logical names and the same file groups. If there’s insufficient space, it kicks out a message. That’s it. Seems to work.