Reading and Writing to SQL Databases with PowerShell

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. Th

Use PowerShell to Check .NET version and update
Fixing Domain Trust Relationships!
Mapped Drive with Encrypted Password

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)
Encrypted Password
Connect to a Remote SQL Server with encrypted password

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
Important!
Encrypted files can ONLY be unencrypted by the current user on the current machine that originally encrypted it. This means that you will not be able to run this script from other user or computer once the password file is encrypted.

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

COMMENTS

WORDPRESS: 0