sqlsql-serverpowershellpowershell-module

How to execute a .sql file using PowerShell

I have a .sql file. I am trying to pass connection string details through a PowerShell script and invoke a .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

  • 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 might be safe to omit based on what your SQL script does.