Skip to content

Recent Articles

13
Feb

Get ServerNames from SQL Server in PowerShell

I see a lot of posts on monitoring sql server that use a “Servers.txt” to iterate the server names.  We (and I imagine a lot of other places as well) store our server information in sql server and not a text file.  Here is how you get said list of servers directly from sql server instead of a text file:

Set-Location SQLSERVER:\SQL\SERVERNAME\DEFAULT
$Servers = Invoke-Sqlcmd -Query "SELECT
       ServerName      
   FROM dbo.[Servers];"
   
foreach($server in $Servers)
{
       Write-Host $server.ServerName
}

You’ll need to load the sql server snapin for this to work correctly.

13
Feb

Compare jobs with PowerShell

I’m more amazed every day at how much easier powershell makes administering sql server.  We currently have this big messy home-grown monitoring system to compare and track changes in sql server.  It’s complicated, fragmented and difficult to debug.  I just duplicated the whole job-compare section of the monitoring system with 9 lines of code.  This will tell you which jobs were added and which ones were removed.  I’ve not tested it thoroughly (nay, at all), like what happens when you change a schedule on a job or whatnot, but this is a good start:

sl SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs
$jobs = Get-ChildItem
$jobsPrevious = Import-Clixml -Path C:\JobsPrevious.xml
$difference = Compare-Object -ReferenceObject $jobsPrevious -DifferenceObject $jobs
$Removed = @($difference | Where {$_.SideIndicator -eq "<="} | foreach {$_.InputObject}) -join ", "
$Added = @($difference | Where {$_.SideIndicator -eq "=>"} | foreach {$_.InputObject}) -join ", "

Write-Host "Removed:  $Removed"
Write-Host ""
Write-Host "Added:  $Added"

This just does the jobs, but there’s no reason why you couldn’t apply this code to other nodes in the SMO object model.

Powershell is amazing.  If you’re not using powershell to administer SQL Server, you’re really doing yourself a HUGE disservice.  Use at your own risk.

Update:  The xml that returns from get-childitem will be huge.  Also note, that you should actually write the $jobs out to file and re-load it using the Import-Clixml in order for the compare-object to compare properly.  I’m STILL working on getting this up to snuff, but I’m currently stretched too thin to dedicate any time to this at the moment.  I’ll put up a better post once I’m done with it, but this is a good starting point.

29
Jan

Powershell script to check if LiteSpeed is installed

You could easily modify this to check if anything is installed really.

 
function CheckLiteSpeed($machineName)
{
       Write-Host $machineName
       $version = "";
       $reg = [WMIClass]"\\$machineName\root\default:stdRegProv"
       $HKEY_LOCAL_MACHINE = 2147483650
 
       $strKeyPath = "SOFTWARE\Imceda\SqlLiteSpeed\Engine"
       $version = $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Version").svalue
       if($version -eq $null)
       {
 
               return "Not Installed"
       }
       return $version
}
 
Clear-Host
$destFile = "C:\LiteSpeedInstall.csv"
$servers = Get-Content "C:\Servers.txt";
 
if(Test-Path($destFile))
{
       Remove-Item -LiteralPath $destFile
}
Add-Content -LiteralPath $destFile "ServerName,LiteSpeedVersion"
 
foreach($server in $servers)
{
 
       $strVersion = "";
       $strVersion = CheckLiteSpeed($server);
       Add-Content -LiteralPath $destFile "$server,$strVersion";
 
}
 

Use at your own risk.  Not rigorously tested.

Bender out.

21
Jan

I want to go to Dallas

I think there was a Redd Foxx joke about going to Dallas…but I digress..

I'm a sponge!

The folks at sqlskills.com are offering up free tuition to their 5-day Internals and Performance class in Dallas, February 21-25.  I would kill for a chance to take this class.  (seriously, email me at bender@dirtydeedsdonedirtcheap.com).  I’m not too sure about the bottomless tea part though.  I would imagine it would burn the nethers badly.

I’ve been absolutely devouring all the content that these guys have been throwing out lately, and would love a chance to learn it straight from the authors themselves.  I’ve watched pretty much all of their MCM training videos (some twice!).  I’ve also been studiously going through the Sql Server 2008 Internals book that they’ve co-authored.  Honestly, I didn’t know how much I didn’t know until I recently committed to learning everything I can about Sql Server.

Now, why I deserve this:

  1. I recently have stepped up into the Senior DBA position at my place of employment, hence my studiousness.
  2. Since undertaking said studiousness, I’ve found a forgotten love for learning.  I’m really really enjoying learning all the internals of Sql Server.  (I’m a born-again DBA of sorts I guess).
  3. My company won’t pay for it.
  4. I want to become a Sql Server MCM.  I’m trying really hard to get to that level.
  5. I just want to know everything about Sql Server!  I’m a sponge.
  6. I’m really excited about learning from Paul, Kimberly, Bob and even Brent!

Bender out.

13
Dec

PowerShell script to get only the most recent backups

Here’s a neat PowerShell script to grab only the most recent backup files.  Given a path, it will recurse the directories and only return the most recent *.bak file in all subdirectories.

$path = "H:\SourceDir"
$destPath = "\\Destination"

function CopyLastBackup
{
    try
    {
        Remove-Item "$destPath\*" -include "*.bak"
        $dirs = Get-ChildItem $path | where {$_.PSIsContainer} | where {$_.GetFiles().Count -ne 0}
        foreach($dir in $dirs)
        {
			$file = Get-ChildItem -LiteralPath $dir.FullName -include "*.bak" | sort CreationTime -Descending | select -First 1
        	Copy-Item -LiteralPath $file.FullName -Destination $destPath -force
            write-host $file 
        }
        return 0;
    }
    catch
    {
        write-host $error[0]
        return 1
    }
    
}


$ret = CopyLastBackup
if($ret -eq 1)
{
    throw "Failure"
}