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

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. :-)

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

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

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

[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