How to Run a SQL script query from power shell

By Charles McDowell

Comptia Certified Linux Networking Professional.

Cisco Certified Networking Technician.

Microsoft Technology Associate Database Administration

View fullsize

“Diplomacy is the art of telling people to go to hell in such a way that they ask for directions.
”

— Aleron Kong, The Land: Raiders

We can run a sql query from power shell using the Invoke-Sqlcmd cmdlet.

The Invoke-Sqlcmd cmdlet will run a script with the commands supported by the SQL Server SQLCMD utility.

The support syntax include Transact-SQL , and XQuery syntax. Also included is Structured query language such as GO and QUIT.

When Invoke-sqlcmd is ran, the first result set that the script returns is displayed as a formatted table.

Example:

ALL Syntax

## SQL configuration
$SQLInstance = "insertserver,insertportnumber"
$SQLDatabase = "insertdatabase"


# Check for sql scripts in a folder, exit program if none there.
$SQLScriptDIR = “Insert folder path that has scripts”
$SQLScripts = Get-ChildItem -Path $SQLScriptDIR
If ([string]::IsNullOrWhitespace($SQLScripts)){ Exit Write-Host "No Scripts in $SQLScriptDIR" }

# Give the SQLScripts object a property we can use to show status later
$SQLScripts | Add-Member -NotePropertyName ProcessingStatus -NotePropertyValue ''


#  Execute scripts in folder and write to console which files excuted. 
ForEach($Script in SQLScripts)
 {
     
                 Write-Host "Executing Script"
                Invoke-Sqlcmd -ServerInstance $SQLInstance  -Database
                $SQLDatabase -InputFile $Script.FullName
                $Script.ProcessingStatus = 'Script Executed'
                Write-Host "$Script.ProcessingStatus"
    #move the file out of the folder to an archive folder to soft delete files from folder
                Move-Item -Path $Script.FullName -Destination 
             ($ConfigurationSQLScriptArchive + $Script.Name) -Force
 }