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?

Solution 1

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.

Solution 2

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

Solution 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

Solution 4

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

Solution 5

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

View more solutions

Comments

[M.C.Rohith]

An addition to this for SQL Server 2012, use SqlServerCmdletSnapin110 and SqlServerProviderSnapin110 for registering.

[Samselvaprabu]

@M.C.Rohith: Thanks for your value addition. Really an useful information

[workabyte]

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

[CB.]

@workabyte Have you read this answer below: stackoverflow.com/a/10923160/520612 ?? ;)

[workabyte]

@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

[thomasb]

Note that you can now download the SQLPS module from MS ; with PowerShellTools.msi, there are a x86 and a x64 version.

[qJake]

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.

[wut-excel]

Get-Module -ListAvalable >> a correct command is Get-Module -ListAvailable.

[LeBleu]

To use PowerShell with SSMS 17 or later, install it from the PowerShell Gallery with Install-Module -Name SqlServer

[Darrin]

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