Category Archives: powershell


I wanted to add a neat demo to my .NET Powers Activate! Form of PowerShell! presentation that would really demonstrate the use of .NET in PowerShell. I decided to have PowerShell create a PowerPoint presentation from code for my opening demo.

It uses New-Object -ComObject to instantiate a PowerPoint.Application COMobject. The code then proceeds to create a presentation and add two slides, with content, to the presentation. Finally it applies a theme to the presentation. I obfuscated the slide text just for fun by encoding to Base64 and storing the encoded string in the variables at the top. The text gets decoded when getting posted to the slides. The code to encode the text is commented at the bottom for completeness.

#Script my PowerPoint slides
#Theme requires PowerPoint 2007 (v12)
$title = 'Lk5FVCBQb3dlcnMgQWN0aXZhdGUhIEZvcm0gb2YgUG93ZXJTaGVsbCE='
$subtitle = 'RXJpYyBIdW1waHJleQ=='
$contactInfo = 'd3d3LmVyaWNodW1waHJleS5jb20KQGxvdHNhaGVscAp3d3cubGlua2VkaW4uY29tL2luL2xvdHNhaGVscAplcmljLmh1bXBocmV5QGdtYWlsLmNvbQ=='
$app = New-Object -ComObject PowerPoint.Application -strict -property @{visible=$true}
$ppt = $app.Presentations.Add($true)
$slide = $ppt.Slides.Add(1, 1)
$slide.Shapes.Title.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($title))
$slide.Shapes | ?{$_.Name -eq 'Subtitle 2'} | %{$_.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($subtitle))}
$slide2 = $ppt.Slides.Add(2, 2)
$slide2.Shapes.Title.TextFrame.TextRange = 'Contact Me'
$slide2.Shapes |
    ?{$_.Name -eq 'Text Placeholder 2'} |
    %{$_.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($contactInfo))}
if(Test-Path 'C:\Program Files (x86)' -PathType Container) {
    $ppt.ApplyTheme("C:\Program Files (x86)\Microsoft Office\Document Themes 12\Module.thmx")
} else {
    $ppt.ApplyTheme("C:\Program Files\Microsoft Office\Document Themes 12\Module.thmx")
#Command used to encode strings to Base64

Presenting for the PASS PowerShell Virtual Chapter

On Wednesday, February 16 at 2:00pm CST I’ll be presenting “.NET Powers Activate! Form of PowerShell!” to the PASS PowerShell Virtual Chapter.

PowerShell’s cmdlets can be pretty powerful on their own, but that’s only part of the power of PowerShell. The .NET Framework is the basis of the PowerShell environment. As such, you have a vast library of operations at your disposal. Learn to use its abilities and save the day without needing a monkey with a bucket.

Update: Here are the files to go along with the presentation. .NET Powers Activate! Form of PowerShell!

Download Video (wmv)

SQL Saturday #57 Slides, Code & Video

This past weekend I spoke at SQL Saturday #57 in Houston, TX.

I want to thank the local PASS chapter HASSUG, the organizers Nancy Wilson (blog | twitter), Jonathan Gardner (blog | twitter), Malik Al-Amin (twitter), the volunteers, the other speakers, and the attendees. It was a great event with a great venue.

I especially want to thank those attendees who chose my sessions out of the many other great ones.

Slides, Code & Video

So What Can I Do With PowerShell?

ORMs for the DBA

Download Video (mp4)
*Thanks to the MidnightDBAs for lending me their mic for this recording.

Automate Downloading SQL Cumulative Updates

Those of us admins that download each SQL Cumulative Update when it comes out may find this script handy. When you request a CU from Microsoft, you get this plain text email with links and passwords. Each link is to a different self-extracting zip file with the corresponding password below it.

When a CU has many files, it becomes a long series of point-and-click to download them all. Then you have to manually put in the password to each file in order to extract it. I wanted an easier solution.

Continue reading

Download the MCM Readiness Videos with PowerShell: Now with BITS

I was playing around with BITS transferring backups between servers and discovered that BITS has PowerShell cmdlets. I was previously using the BITSAdmin Tool which was easy, but I wanted to find out more about the PoSh cmdlets. After reading the docs, I decided to use these cmdlets in my MCM video download script.

BITS stands for Background Intelligent Transfer Service. It is a service built into windows since XP designed to transfer files in the background without interrupting normal user network usage. Windows Update uses BITS to download all the patches that need to be applied to your computer. As stated above, you too can also use BITS to transfer files around. If you regularly transfer large files over the network and get annoyed by how it affects your computer experience, look into replacing the normal copy, xcopy, robocopy commands with it.

My first attempt queued all the files into one BITS transfer. The upside is only one BITS job to manage. The downside is the filenames aren’t written until the whole job completes. Long download jobs often get cancelled and the user would want to be able to listen to or watch the files that had already finished. I wanted the opposite, so I went for one job per file.

The BITS cmdlets require PowerShell v2.0

Download the script

Splitting SQL Server Logs by ProcessInfo

I wanted a way to parse through my SQL logs and be able to look at one particular spid or ProcessInfo class such as Server, Backup, Logon, etc. I came up with the following PowerShell script: *You must have either trace flag 1204 and/or 1222 turned on. (More info)

if($path -eq $null) {exit}
if(Test-Path $path) {
    "Parsing $path"
    $spid = $null
    gc $path | %{
        #if ($_.Length -gt 35 -and $_.Substring(24, 12).StartsWith("spid")) {
        if($_ -match "^\d{4}") {
            $spid = $_.Substring(24, 12).Trim().PadRight(12, '_')
        } else {
            if($_.Trim().Length -lt 1 -and $spid -ne $null) {}
            else {
                $spid = "Default".PadRight(12, '_')
        $outfile = $path.Substring(0, $path.LastIndexOf(".")) + [string]::Format(".{0}.txt", $spid)
        $_ | Out-File -FilePath $outfile -Encoding "ASCII" -Append
} else {"Bad path given"}

If you dump your log file via the following command, this works really well.
sqlcmd -S MyServer\Inst1 -Q "xp_ReadErrorLog" -o myserver.log

Once you have it, just call the script and specify the -Path parameter as such:
& '.\Split SQL Log by ProcessInfo.ps1' -Path C:\path\to\myserver.log

It looks at each line and figures either the spid or processinfo class and puts the line into a file with that string in its name. If it sees a blank line, it will put it into the previous file. WARNING: This could result any many files.

Yes, you could dump this to SQL or parse this some other way, but I wanted to split it into files so I can run it through another process to get all deadlock information that I’ll be posting later.

Download script

Download the MCM Readiness Videos with PowerShell

Microsoft has changed their SQL MCM program to allow more people access to the course and certification. In doing so, they are recording much of the classroom training that was previously required and putting it up on TechNet. We’re talking close to 60 videos already. Sure, you could stream them one at a time, but I wanted to be able to download them all and watch them offline without a bunch of clicking. So I built the below PowerShell scripts. They’re really simple to use, and only have one external requirement that makes dealing with web pages much easier.

You’ll need to download the Html Agility Pack library from here.

Get the script


Get urls of all WMV files
>& ‘.\Download MCM Videos.ps1’

Download all WMV files
>& ‘.\Download MCM Videos.ps1’ -download

Download all iPod files
>& ‘.\Download MCM Videos.ps1’ -download -type ipod

Getting VLF stats for multiple databases

After watching Paul Randal’s (blog | twitter) MCM video on log files, I wanted to find a quick way to get VLF (Virtual Log File) statistics across multiple databases and servers. I went two routes, PowerShell and T-SQL. Our internal monitoring uses T-SQL already, so I wanted to be able to easily work with the existing framework. I wanted to create a PowerShell version, as some others might find it useful and I just like working with PowerShell.

T-SQL Version
I wound up making my table structure the same as David Levy in this blog post. Michelle Ufford has a very similar script at her blog post.

PowerShell Version
Thanks to Aaron Nelson (blog | twitter) for giving me an idea to tweak this.

More on VLFs:

Active / Active Cluster Server Memory Allocation

We have several active / active clusters with multiple instances per node in our environment. We want to ensure the instances are spread as evenly as possible across the nodes. I wrote a quick PowerShell script to go through a text file and get the physical node where the instance is running, how much physical memory the node has and how much memory the instance is configured to use.

Just create a text file “servers.txt” with the list of instances on a cluster and run the following.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$servers = get-content "servers.txt"
foreach($server in $servers)
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $server
[String]::Format("{0} uses {1}MB RAM. Resides on {2}.", $server, $sqlServer.Configuration.MaxServerMemory.ConfigValue, $sqlServer.ComputerNamePhysicalNetBIOS)
$objCS = Get-WmiObject Win32_ComputerSystem -ComputerName $server.Substring(0, $server.IndexOf("\"))
[String]::Format("{0}\{1}: {2}MB", $objCS.Domain, $objCS.Name, [Math]::Round($objCS.TotalPhysicalMemory / 1mb))
The formatting leaves much to be desired, but it’s a quick check and that was what I was interested in. Excel can do all the formatting and calculating later.