MSSQL

PowerShell SQL Query Function

The function below can be used to a query to a database. The script returns any error as a result (2 lines).

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

 

PowerShell Add SQL Modules

Modules can now be added by using Import-Module. The following example list all the available.

Get-Module -ListAvailable

Load the SQLPS module (if available)….

Import-Module -Name "SQLPS"

Before you can import the SQLPS powershell module you need to install SQL Management Studio or install the SQLSysCLRTypes, SharedManagementObjects and PowerShellTools packages from the SQL Server Feature Pack.

PowerShell Check Database-backup

This next script can be used to check if Full and Transaction Log backups are made within the last two days. Databases are read from a CSV file. I use a slightly modified version which reads databases from a registration database.

# ********************************************************************
# Scriptnaam: CHECKBACKUP.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20110811A
# Info: Check if backup is made within last two days...
# ********************************************************************

# Global config settings...
[string]$sServerInstanceFile = ".ServerInstance.csv"
[string]$sSMTPServer1 = "smtp-server-name"
[string]$sToAddress1 = "email-adsress"

# Global objects...
$oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()

# Register Snapins...
$oPSSnapinRegResult = PSSnapin SqlServerCmdletSnapin* -registered
if ($oPSSnapinRegResult) {
  $oPSSnapinResult = PSSnapin $oPSSnapinRegResult.Name
  if (-not $oPSSnapinResult) { 
    Add-PSSnapin $oPSSnapinRegResult.Name
  }
} else {
  break
}
  
# Functions and Procedures...
function fSendMail([string]$sSMTPSrvrName, [string]$sFrom, [string]$sTo, [string]$sSubject, [string]$sBody, [string]$sAttFileName) {
  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      return "INFO: Email has been sent."
    } else {
      return "ERROR: Error sending e-mail."
    }

  } else {
    return "ERROR: Argument(s) missing. [fSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" (`"Body`") (`"AttachFileName`")]"
  }
}

function fSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" `
                               + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; `
                               if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

# Start Main script...
[int]$iCounter = 0
Import-Csv $($sServerInstanceFile) -header ("instance", "checkfull", "checktl") | foreach {
  # Skip header...
  if (($iCounter -ne 0) -and ($_.instance)) {
    $oResult1 = $null
    $oResult2 = $null
    # Run Queries on servers...
    if ($_.checkfull -eq 1) {
      [string]$sQuery1 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentFullBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE ((msdb..backupset.type = 'D' OR msdb..backupset.type = 'I') `
      AND msdb..backupset.is_copy_only = 0 `
      AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.state = 0 `
      AND sys.databases.name  'tempdb' `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult1) = fSQLCmd $($_.instance) $sQuery1 "master"
    }


    if ($_.checktl -eq 1) {
      [string]$sQuery2 = "/* ##### Begin T-SQL Query... ##### */ `
      SELECT sys.databases.name AS NoRecentTLBackup `
      FROM sys.databases `
      WHERE sys.databases.name NOT IN `
      (SELECT DISTINCT sys.databases.name from sys.databases `
      INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name `
      WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2))) `
      AND sys.databases.recovery_model != 3 and sys.databases.state = 0 `
      /* ##### Einde T-SQL Query... ##### */"
      # Forceer in een object zodat we altijd kunnen tellen...
      [Object[]]($oResult2) = fSQLCmd $($_.instance) $sQuery2 "master"
    }

    # Zodra er rows worden geteld een e-mail samenstellen...
    if (($oResult1.Count -gt 0) -or ($oResult2.Count -gt 0)) {
      $oResult1 = $oResult1 | Out-String
      $oResult2 = $oResult2 | Out-String
      [string]$sFromAddress1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
      [string]$sBody1 = $oResult1 + $oResult2
      [string]$sSubject1 = $($_.instance.Replace("", "_")) + ":Database backup older than 2 days..."
      fSendMail $sSMTPServer1 $sFromAddress1 $sToAddress1 $sSubject1 $sBody1
    }
  }
  $iCounter++
}
# ********************************************************************

CSV File format:

instance,checkfull,checktl
SERVERA,1,1
SERVERBINS1,1,1
SERVERC,1,1

PowerShell Create MSSQL Agent Job

M.b.v. onderstaande PowerShell script voorbeeld kun je, door gebruik te maken van SQL PowerShell Modules, een SQL Agent Job aanmaken t.b.v. MS SQL Server.

$oSRV=Get-Item SQLSERVER:\SQLSERVERNAAM\INSTANCENAAM

# Maak een SQL Agent Job...
$oJOB=New-Object Microsoft.SqlServer.Management.Smo.Agent.Job
$oJOB.parent=$oSRV.Jobserver
$oJOB.Name="Backup System Database Job"
$oJOB.Create()

# Maak een JobStep t.b.v. backup master database in de nieuwe Job...
$oStep1=New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep
$oStep1.parent=$oJOB
$oStep1.Name="Master Backup"
$oStep1.SubSystem="TransactSQL"
$oStep1.Command="BACKUP DATABASE [master] TO DISK='X:\Path\To\File.bak'"
$oStep1.OnSuccessAction = "GotoNextStep"
$oStep1.Create()

# Maak een JobStep t.b.v. backup master database in de nieuwe Job...
$oStep2=New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep
$oStep2.parent=$oJOB
$oStep2.Name="Model Backup"
$oStep2.SubSystem="TransactSQL"
$oStep2.Command="BACKUP DATABASE [model] TO DISK='X:\Path\To\File.bak'"
$oStep2.OnSuccessAction = "GotoNextStep"
$oStep2.Create()

PowerShell T-SQL Scripting

Article written in the Dutch language…

Met onderstaande commando’s kun je d.m.v. T-SQL gegevens opvragen uit een MSSQL database m.b.v. SQL PowerShell Modules of SQLPS.

$sResult = Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS Servername;" `
                         -QueryTimeout 3 `
                         -ServerInstance [SERVERNAME][INSTANCE] `
                         -Database master

Vervolgens vraag je de inhoud van bovenstaande voorbeeld d.m.v. variable $sResult.Servername. Bij een een resultaat van meer dan een record worden de values opgeslagen in een array. In dat geval geeft de variabele $sResult.count het aantal geretourneerde regels terug (-gt 1). Je kunt dan bijvoorbeeld de eerste regel van het resultaat opvragen d.m.v. $sResult[0].columnname. Je kunt m.b.v. onderstaande statement afvangen of het resultaat uit meer dan twee regels bestaat.

if ($sResult.count -gt 1) {
  # TestVoorbeeld afhandelen Array...
  write-host "Er zitten " $sResult.count " values in de array..." 
  foreach ($oResultItem in $sResult) {
    $oResultItem.Servername
  }
} else {
  # Afhandelen string value...
  $sResult.Servername
}

Ook is het mogelijk vanuit een specifieke context gegevens op te vragen.

cd SQLSERVER:SQL{servername}{instancename}Databasesmsdb
Invoke-Sqlcmd -Query "SELECT * FROM sysjobs;" -QueryTimeout 3