Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.
Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its today need to learn at least basic of some PS cmdlets.
In this tutorial, we will walk you through most IMP cmdlets Invoke-Sqlcmd to run/execute SQL command using PowerShell.
Note:- Please click here to get 9th part of PowerShell –SQL Automate tutorial.
To get more information about PowerShell cmdlets, you can refer this link.
Installing SQL server module first on server:-
Here, we want to manage/administer SQL server using PowerShell hence we will have to first make sure that SQL server module should be installed on respective server through which you are trying to connect or run PS scripts.
Please refer this link for the same–> you will need administrator permission to install the module.
Invoke-Sqlcmd
Run a script containing statements supported by the SQL Server SQLCMD utility. .
Syntax
Invoke-Sqlcmd [-ServerInstance PSObject] [-Database String] [-EncryptConnection]
[-Username String] [-Password String] [[-Query] String]
[-QueryTimeout Int32] [-ConnectionTimeout Int32] [-ErrorLevel Int32]
[-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
[-AbortOnError] [-DedicatedAdministratorConnection] [-DisableVariables]
[-DisableCommands] [-HostName String] [-NewPassword String]
[-Variable String[]] [-InputFile String] [-OutputSqlErrors Boolean]
[-IncludeSqlUserErrors] [-SuppressProviderContextWarning] [-IgnoreProviderContext]
[-OutputAs OutputType] [CommonParameters]
Invoke-Sqlcmd [[-Query] String] [-QueryTimeout Int32] [-ErrorLevel Int32]
[-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
[-AbortOnError] [-DisableVariables] [-DisableCommands] [-Variable String[]]
[-InputFile String] [-OutputSqlErrors Boolean] [-IncludeSqlUserErrors]
[-OutputAs OutputType] -ConnectionString String [CommonParameters]
Example:
Run a script and send the output to a file:
PS C:\> Invoke-Sqlcmd -InputFile “C:\test\test.sql” -ServerInstance “SQLserver\Instance”| Out-File -FilePath “C:\test\outputlist.txt”
Education is the best friend. An educated person is respected everywhere. Education beats the beauty and the youth
Chanakya
ADsql-Tech Forever 
Pingback: PowerShell –SQL Automate Tutorial-11-Find SQL services running Service Account for Audit Purpose – ADsql-An Independent DB Consultant {24*7 Remote Database Support}