SQL Server and powershell

  • Article

Here is some sample of what you can achieve to manager SQL Server with powershell, let start with system script :

1) is the SQL Service running ?

Get-WmiObject win32_service | Where-Object {$_.name -match "^*SQL*"} | select SystemName, Name, StartName, State

2) how the process run ?

get-wmiobject win32_process | where {$_.name -like "*sql*"} | select name, workingsetsize, threadcount

3) Is there info about SQL Server in the event log ?

get-eventlog -logname application -newest 1000 | where-object {($_.source -eq "MSSQL`$R2") -and ($_.EntryType -eq "Error")}

 

Now, if we want info about SQL Server, we have to connect to SQL server, to do that, we must first load the SMO assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

and make a connection to a specific SQL Server instance :

$server = New-Object('Microsoft.SqlServer.Management.Smo.Server') myserver\myinstance

 4) SQL Server instance information :

$server | select Edition, Version, ProductLevel, Collation, PhysicalMemory, Processors
$server.Configuration.get_AweEnabled()

5) Processes Info (sp_who):

$server.EnumProcesses() | where-object {$_.IsSystem -eq $false} | select Spid, Login, Host, Status, Command, database, Cpu, memusage, BlockingSpid | Format-Table

6) databases information :

$server.Databases | select name, collation, size

To go in a specific database :

$db = $server.databases["DatabaseName"]

and to show all the info about this database :

$db

7) Tables information :

foreach ($t in $db.Tables)
{
write-host $t.name, $t.RowCount, $t.DataSpaceUsed, $IndexSpaceUsed
}

8) Tables with clustered index :

foreach ($t in $db.Tables)
{
 if ($t.HasClusteredIndex -eq $false)
  {
  write-host $t.name
 }
}

9) script an object

you can generate the object's SQL Script (in this example, a table, but it work for all SQL Server object, stored proc etc...)

$db.Tables["tableName"].script()

10) Detach/attach a database :

#Detach :

$server.DetachDatabase("Mydatabase", $false)

#Attach
$sc = new-object System.Collections.Specialized.StringCollection
$sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Mydatabase.mdf")
$server.AttachDatabase("myDataBase", $sc)