6 thoughts on “Test your Powershell prowess”

1. # All scripts run from the SQLSERVER:\SQL\HostName\InstanceName provider folder
   #
   # Note on number 4 – Not sure if I’ve overlooked a way to do this in the SQLPS provider without using to raw SMO

   #1. Get the total size of all the DBs on a server.

   get-childitem Databases | Select Name,Size

   #2. Cycle through a list of servers and get the service account that all the SQL services are set to start with.

   $servers = get-content .\SQLServers.txt
   get-wmiobject win32_service -computerName $servers -filter “Name like ‘%SQL%'” | Select SystemName,Name,StartName,Caption

   #3. Script all the SPs in a DB to a txt file. Make sure they’re separated by GO stmts or the script won’t run.

   $databaseName = “YourDatabaseName”
   $scriptText = “”
   get-childitem “Databases\$databaseName\StoredProcedures” |% {$scriptText += $_.Script(); $scriptText += “GO”}
   set-content .\ScriptFile.txt $scriptText

   #4. Change the default file location for a group of servers.

   $dataLocation = “D:\MSSQL\Data”
   $logLocation = “D:\MSSQL\Log”
   $servers = get-content .\SQLServers.txt
   $servers |% {`
   $smo = New-Object Microsoft.SqlServer.Management.Smo.Server -argumentList $_;
   $settings = $smo.Settings;
   $settings.DefaultFile=$dataLocation;
   $settings.DefaultLog=$logLocation;
   $settings.Alter();
   }

   #5. Cycle through all the jobs on a server and change all their owners to sa.

   get-childitem “JobServer\Jobs” |% {$_.OwnerLoginName = “sa”;$_.Alter()}

2. hey tmc_au… thanks for turning in your answers. Here are my comments:

   1. The question was to get the total size of all DBs. You only got their individual sizes.
   2. Correct.
   3. There’s definitely more than one way to do this and you got one.
   4. Y, while your way will get the job done, there’s a much shorter version I think you’ll like.
   5. Yep, exactly how I would have done it too.

   I’ll publish all my answers soon. I urge you to get away from the old style thinking where you’re using the raw SMO objects. There are only limited circumstances where they’re necessary and this definitely isn’t one of them. However, that you can do it puts you far ahead of the curve.

3. Pingback: Powershell Prowess Tested « 36 Chambers – The Legendary Journeys: Execution to the max!

4. I’m actually in the process of watching your Powershell videos, so that’s a good head start on answering these. For all five tasks, I just decided to use the standard Powershell window rather than SQL Server Powershell.

   #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 in one step:
   $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

   #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() }

5. Re: #1 Oops! read the question wrong. That would be:
   get-childitem Databases | measure-object Size -Sum | select-object @{Name=”TotalSizeMB”;Expression={$_.Sum}}

   Re: #4 Yeah using SMO in Powershell for this just felt really wrong as there has to be easier way to alter simple server properties like this. After hunting around in the provider docs and in the provider itself for a while looking for the ‘Settings’ object I still hadn’t stumbled across it. I’m sure it’ll be an Aha moment in the follow up post!

6. Pingback: DBA Rant » Archive » Powershell Homework Answers

Comments are closed.