Reading and Writing to SQL Databases with PowerShell

First let’s start off by saying that this is not meant to be the end to end all solutions especially for large databases with thousands of records. This is a great starting point to demonstrate to you that you can use powershell to automate many things with SQL including logging for your scripts.

CTIGEEK over at Github also wrote a very nice module for sql server that does a lot of the heavy lifting (https://github.com/ctigeek/InvokeQueryPowershellModule)

Connecting to SQL Server:

• Install-Module -Name SqlServer -AllowClobber  (Allow-Clobber provides the ability to overwrite any existing module)
• Get-SqlDatabase -ServerInstance “.\SQLExpress”  (connecting to local SQL server with Windows logged in credentials)

Create an encrypted password file then we pass that to a SQL connection command:

(Get-Credential).Password | ConvertFrom-SecureString | Out-File "C:\Scripts\Password.txt"
$encrypted = Get-Content c:\Scripts\password.txt | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential("sqlaccount", $encrypted)
Get-SqlDatabase -ServerInstance "SQL1" -Credential $cred

Reading data from SQL

(Get-Credential).Password | ConvertFrom-SecureString | Out-File "C:\Scripts\Password.txt"
$encrypted = Get-Content c:\Scripts\password.txt | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential("sqlaccount", $encrypted)
Get-SqlDatabase -ServerInstance "SQL1" -Credential $cred
$Instance = "SQL1"
$Database = "Contoso"
$Table = "Customers"
Read-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -TableName $Table -Credential $cred

Filter and Views

Basic filtering can be done in PS, if more advanced need to be done then create a view which can then be read by PS.

$Instance = "SQL1"
$Database = "Contoso"
$Table = "Customers"
# Use this to read only specific columns in a table, ex. Name and Salary
Read-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -TableName $Table -columnname Name, Salary -Credential $cred
# Use this to only show matching data in a specific column
Read-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -TableName $Table -Credential $cred | Where {$_.Name -eq "Jeff"}
# Use this to read data from a SQL View
$View = "SimpleView"
Read-SqlViewData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -ViewName $View

Write data to existing SQL Table

(Get-Credential).Password | ConvertFrom-SecureString | Out-File "C:\Scripts\Password.txt"
$encrypted = Get-Content c:\Scripts\password.txt | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential("sqlaccount", $encrypted)
Get-SqlDatabase -ServerInstance "SQL1" -Credential $cred
$Instance = "SQL1"
$Database = "Contoso"
$Table = "Customers"
# Create a Hash Tag of information to add to the table
$Insert = @(
[ordered]@{
ID = 7
NAME = 'Jeff'
AGE = 20
ADDRESS = 'Menifee'
SALARY = 5000
}
[ordered]@{
ID = 8
NAME = 'Kathleen'
AGE = 20
ADDRESS = 'Menifee'
SALARY = 5500
})
$Insert.Foreach({$_.ForEach({[PSCustomObject]$_}) | Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database
-SchemaName dbo -TableName $Table -Credential $cred})

Creating a New SQL Table

(Get-Credential).Password | ConvertFrom-SecureString | Out-File "C:\Scripts\Password.txt"
$encrypted = Get-Content c:\Scripts\password.txt | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential("sqlaccount", $encrypted)
Get-SqlDatabase -ServerInstance "SQL1" -Credential $cred
$Instance = "SQL1"
$Database = "Contoso"
$Table = "Customers"
# This example get all computer information and creates the table and columns with data
(Get-Process | Select-Object -Property ID, ProcessName, StartTime, UserProcessorTime, WorkingSet, Description) | Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -TableName $Table -Force

# Import CSV and create a table
Import-Csv -Path c:\Scripts\users.csv | Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName "dbo" -TableName $Table -Force