How to execute sql file using powershell

Question

I have a  sql file  I am trying to pass connection string details through a Powershell script and invoke an  sql file   I was searching and came up with a cmdlet related to Invoke-sqlcmd  While I was trying to find a module corresponding to SQL  I did not find any one in my machine   Should I install anything in my machine  the machine already has SQL Server Management Studio 2008 R2  to get the modules or is there any easy way to execute  sql files using Powershell

User · Answer

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

User · Answer

Here is a function that I have in my PowerShell profile for loading SQL snapins   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

User · Answer

with 2008 Server 2008 and 2008 R2  Add-PSSnapin -Name SqlServerCmdletSnapin100  SqlServerProviderSnapin100   with 2012 and 2014  Push-Location Import-Module -Name SQLPS -DisableNameChecking Pop-Location

User · Answer

Try to see if SQL snap-ins are present   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

User · Answer

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   -

[sql] How to execute .sql file using powershell?

Examples related to sql

Examples related to sql-server

Examples related to powershell

Examples related to powershell-module