How to execute .sql file using powershell?

Question

I have a file. sql . I am trying to pass the details of a link string through a Powershell script and call the .sql file.

I searched and came up with a cmdlet related to Invoke-sqlcmd . While I was trying to find a module that matches SQL, I did not find it on my machine.

Do I have to install something on my computer (the machine already has SQL Server Management Studio 2008 R2) to get the modules or is there an easy way to execute .sql files using Powershell?

Answer 1

Try to see if SQL snap-ins are available:

 get-pssnapin -Registered Name : SqlServerCmdletSnapin100 PSVersion : 2.0 Description : This is a PowerShell snap-in that includes various SQL Server cmdlets. Name : SqlServerProviderSnapin100 PSVersion : 2.0 Description : SQL Server Provider 

If so

 Add-PSSnapin SqlServerCmdletSnapin100 # here lives Invoke-SqlCmd Add-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. 

Answer 2

Quote from Import SQLPS Module on MSDN,

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

So, yes, you could use the Add-PSSnapin approach described in detail by Christian, but it is also useful to evaluate the recommended approach of the sqlps module.

The simplest case assumes that you have SQL Server 2012: sqlps is included in the installation, so you just download the module, like any other (usually in profile ) through Import-Module sqlps . You can check if a module is available on your system using Get-Module -ListAvailable .

If you do not have SQL Server 2012, you just need to load the sqlps module into the modules directory so that Get-Module / Import-Module will find it. Curiously, Microsoft does not make this module available for download! However, Chad Miller kindly packed the necessary items and provided this download module . Unzip it to your ... Documents \ WindowsPowerShell \ Module Directory and continue importing.

It is interesting to note that the modular approach and the snapin approach are not identical. If you download snapins, run Get-PSSnapin (without the -Registered parameter to show only what you downloaded) you will see snapins SQL. If, on the other hand, you download the sqlps Get-PSSnapin , it will not show the loaded snapins, so various blog entries that check the Invoke-Sqlcmd cmdlet only by checking the snapins can produce a false negative result.

2012.10.06 Update

For complete information on the sqlps module and the sqlps mini-shell vs. snap-in SQL Server check out my two-part mini-series Practical PowerShell for SQL Database Developers and Administrators, recently published on Simple-Talk.com, where I, according to one reader comment, successfully "confused" the problem. :-)

Answer 3

 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 

Answer 4

Here is the function that I have in my PowerShell profile for loading snapins SQL:

 function Load-SQL-Server-Snap-Ins { try { $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps" if (!(Test-Path $sqlpsreg -ErrorAction "SilentlyContinue")) { throw "SQL Server Powershell is not installed yet (part of SQLServer installation)." } $item = Get-ItemProperty $sqlpsreg $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) $assemblyList = @( "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SmoExtended", "Microsoft.SqlServer.Dmf", "Microsoft.SqlServer.WmiEnum", "Microsoft.SqlServer.SqlWmiManagement", "Microsoft.SqlServer.ConnectionInfo ", "Microsoft.SqlServer.Management.RegisteredServers", "Microsoft.SqlServer.Management.Sdk.Sfc", "Microsoft.SqlServer.SqlEnum", "Microsoft.SqlServer.RegSvrEnum", "Microsoft.SqlServer.ServiceBrokerEnum", "Microsoft.SqlServer.ConnectionInfoExtended", "Microsoft.SqlServer.Management.Collector", "Microsoft.SqlServer.Management.CollectorEnum" ) foreach ($assembly in $assemblyList) { $assembly = [System.Reflection.Assembly]::LoadWithPartialName($assembly) if ($assembly -eq $null) { Write-Host "`t`t($MyInvocation.InvocationName): Could not load $assembly" } } Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0 Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30 Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000 Push-Location if ((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null) { cd $sqlpsPath Add-PsSnapin SqlServerProviderSnapin100 -ErrorAction Stop Add-PsSnapin SqlServerCmdletSnapin100 -ErrorAction Stop Update-TypeData -PrependPath SQLProvider.Types.ps1xml Update-FormatData -PrependPath SQLProvider.Format.ps1xml } } catch { Write-Host "`t`t$($MyInvocation.InvocationName): $_" } finally { Pop-Location } } 

Answer 5

since 2008 Server 2008 and 2008 R2

 Add-PSSnapin -Name SqlServerCmdletSnapin100, SqlServerProviderSnapin100 

from 2012 and 2014

 Push-Location Import-Module -Name SQLPS -DisableNameChecking Pop-Location