How to execute .sql file using powershell?

Try to see if SQL snap-ins are present:

get-pssnapin -RegisteredName        : SqlServerCmdletSnapin100PSVersion   : 2.0Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.Name        : SqlServerProviderSnapin100PSVersion   : 2.0Description : SQL Server Provider

If so

Add-PSSnapin SqlServerCmdletSnapin100 # here lives Invoke-SqlCmdAdd-PSSnapin SqlServerProviderSnapin100

then you can do something like this:

invoke-sqlcmd -inputfile "c:\mysqlfile.sql" -serverinstance "servername\serverinstance" -database "mydatabase" # the parameter -database can be omitted based on what your sql script does.

Quoting from Import the SQLPS Module on MSDN,

The recommended way to manage SQL Server from PowerShell is to import the sqlps module into a Windows PowerShell 2.0 environment.

So, yes, you could use the Add-PSSnapin approach detailed by Christian, but it is also useful to appreciate the recommended sqlps module approach.

The simplest case assumes you have SQL Server 2012: sqlps is included in the installation so you simply load the module like any other (typically in your profile) via Import-Module sqlps. You can check if the module is available on your system with Get-Module -ListAvailable.

If you do not have SQL Server 2012, then all you need do is download the sqlps module into your modules directory so Get-Module/Import-Module will find it. Curiously, Microsoft does not make this module available for download! However, Chad Miller has kindly packaged up the requisite pieces and provided this module download. Unzip it under your ...Documents\WindowsPowerShell\Modules directory and proceed with the import.

It is interesting to note that the module approach and the snapin approach are not identical. If you load the snapins then run Get-PSSnapin (without the -Registered parameter, to show only what you have loaded) you will see the SQL snapins. If, on the other hand, you load the sqlps module Get-PSSnapin will not show the snapins loaded, so the various blog entries that test for the Invoke-Sqlcmd cmdlet by only examining snapins could be giving a false negative result.

2012.10.06 Update

For the complete story on the sqlps module vs. the sqlps mini-shell vs. SQL Server snap-ins, take a look at my two-part mini-series Practical PowerShell for SQL Server Developers and DBAs recently published on Simple-Talk.com where I have, according to one reader's comment, successfully "de-confused" the issue. :-)

if(Test-Path "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS") { #Sql Server 2012    Import-Module SqlPs -DisableNameChecking    C: # Switch back from SqlServer} else { #Sql Server 2008    Add-PSSnapin SqlServerCmdletSnapin100 # here live Invoke-SqlCmd}Invoke-Sqlcmd -InputFile "MySqlScript.sql" -ServerInstance "Database name" -ErrorAction 'Stop' -Verbose -QueryTimeout 1800 # 30min