Sean McCown has a quick Powershell test for DBAs.  Here are my answers, as a Powershell program.  After the fold, so you don’t cheat.

#Used for all tests
Import-Module SqlServer
$servers = "ROGUE", "GAMBIT", "HUSK", "JUGGERNAUT"
$singleserver = "ROGUE"

#1)  Get the total size of all DBs on a server
$svr = Get-SqlServer $singleserver
$totalsize = 0;

$svr.Databases | %{ $totalsize += $_.Size };
Write-Host $totalsize
#Here is an alternate way to do number 1:
#$svr.Databases | %{ $_.Size } | Measure-Object -sum | Select-Object Sum | Format-Table -autosize

#2)  Cycle through a list of servers and get the service account that all SQL services are set to start with
$servers | %{ Get-WmiObject win32_service -computer $_ -filter "name like '%SQL%'" } | Select-Object SystemName, Name, StartName | Format-Table -autosize

#3)  Script all SPs in a DB to a text file, with each SP separated by a GO statement
$svr = Get-SqlServer $singleserver
$DatabaseToScript = "Warehouse"
$OutputFile = "D:\Temp\output.txt"

$svr.Databases | Where-Object { $_.Name -eq $DatabaseToScript } | %{ $_.StoredProcedures } | Where-Object { $_.IsSystemObject -eq $false } | %{ $_.TextHeader + $_.TextBody + [System.Environment]::NewLine + "GO" + [System.Environment]::NewLine } > $OutputFile
##This version is if you are using the SQL Server Powershell.
#Get-ChildItem Databases\$DatabaseToScript\StoredProcedures | %{ $_.TextHeader + $_.TextBody + [System.Environment]::NewLine + "GO" + [System.Environment]::NewLine } > $OutputFile

#4)  Change the default file location for a group of servers
$NewDefaultFile = "D:\Databases"
$NewDefaultLog = "L:\Logs"

$servers | %{ $s = Get-SqlServer $_; $s.DefaultFile = $NewDefaultFile; $s.DefaultLog = $NewDefaultLog; $s.Alter() }

#5)  Cycle through all jobs on a server and change all owners to sa
$svr = Get-SqlServer $singleserver

$svr.JobServer.Jobs | %{ $_.OwnerLoginName = "sa"; $_.Alter() }
##This version is if you are using the SQL Server Powershell
#Get-ChildItem JobServer\Jobs | %{ $_.OwnerLoginName = "sa"; $_.Alter() }

The script requires SQLPSX to be installed, and I am assuming that you are starting from a standard Powershell window rather than from the SQL Server Powershell instance.  I did leave a couple of those answers in, though, because it’s just so convenient for certain tasks…

Leave a comment