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…