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?

score 97 · Accepted Answer · edited Oct 01 '14 at 13:28

97

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.

edited Oct 01 '14 at 13:28

Diana Ionita

3,251
3
27
42

answered Jun 05 '12 at 10:09

CB.

58,865
9
159
159

6

An addition to this for SQL Server 2012, use SqlServerCmdletSnapin110 and SqlServerProviderSnapin110 for registering. – M.C.Rohith Jun 06 '12 at 05:08
@M.C.Rohith: Thanks for your value addition. Really an useful information – Samselvaprabu Jun 06 '12 at 10:02
2

Just an FYI as im sure many people stumble onto the question.With the newer versions of SQL they have sqlps. you can just add Import-Module “sqlps” -DisableNameChecking to your script – workabyte May 08 '14 at 21:35
1

@workabyte Have you read this answer below: http://stackoverflow.com/a/10923160/520612 ?? ;) – CB. May 09 '14 at 07:27
@CB. lol nope thats why i put the comment here, this answer is at the top and has a lot of votes so i added the comment somewhere people would see it unlike the answer below. Thanks for pointing it out lmao – workabyte May 09 '14 at 13:22
1

PSA: For newer versions of PowerShell, you may want to try `Imoprt-Module SQLPS` instead, or `Get-Module -List | ? Name -eq sqlps` to see if you have one or more SQLPS modules available to import. – qJake Oct 20 '15 at 16:21
I have "Microsoft SQL Server 2019" installed, and find that you do NOT refer to 'SQLPS' anymore, instead, to import the module: Import-Module sqlserver -DisableNameChecking – Darrin Sep 23 '21 at 17:54

Michael Sorens · Answer 2 · 2017-09-11T05:40:13.560

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

Note that you can now [download the SQLPS module from MS](http://www.microsoft.com/en-us/download/details.aspx?id=35580) ; with `PowerShellTools.msi`, there are a x86 and a x64 version. — thomasb, Mar 09 '15 at 15:09
`Get-Module -ListAvalable` >> a correct command is `Get-Module -ListAvailable`. — wut-excel, Sep 11 '17 at 04:58
To use PowerShell with SSMS 17 or later, install it from the PowerShell Gallery with `Install-Module -Name SqlServer` — LeBleu, Oct 09 '18 at 20:06

score 7 · Answer 3 · answered Sep 11 '13 at 17:27

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

score 5 · Answer 4 · answered Sep 12 '13 at 11:30

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
    }
}

score 5 · Answer 5 · answered Mar 08 '21 at 20:44

Here's a light weight approach for simple scripts that requires no additional tools / setup / PowerShell add-ons.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connectionStringGoesHere
$conn.Open()
$content = Get-Content $scriptFileNameGoesHere
$cmds = New-Object System.Collections.ArrayList
$cmd = ""
$content | foreach {
    if ($_.Trim() -eq "GO") { $cmds.Add($cmd); $cmd = "" } 
    else { $cmd =  $cmd + $_ +"`r`n" }
}
$cmds | foreach {
    $sc = New-Object System.Data.SqlClient.SqlCommand 
    $sc.CommandText = $_
    $sc.Connection = $conn
    $sc.ExecuteNonQuery()
}

score 1 · Answer 6 · answered Mar 04 '16 at 16:30

1

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

answered Mar 04 '16 at 16:30

emekm

742
5
9

score 0 · Answer 7 · answered May 25 '22 at 20:39

0

Invoke-Sqlcmd -Database MyDatabase -Query "exec dbo.sp_executesql N'$(Get-Content "c:\my.sql")'"

answered May 25 '22 at 20:39

sipsorcery

30,273
24
104
155

How to execute .sql file using powershell?

7 Answers7

Linked