How to execute .sql file using powershell?

sql sql-server powershell powershell-module
Samselvaprabu picture Samselvaprabu · Jun 5, 2012 · Viewed 131.3k times · Source

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?

Answer

CB. picture CB. · Jun 5, 2012

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.

Related questions

How to format SQLCMD output

I am using below command line to run a SQL query using SQLCMD sqlcmd -S Server -Q "select top 100 * From people" -d people -t 10 The table has 20 columns and when i look at output command line window wraps the text …

Read More
Error restoring database backup to new database with smo and powershell

Taking a database backup from another server I'm trying to restore to sqlexpress on the localhost. This restore will work via the gui but I'm having issues restoring it with powershell. I get the following error message: Exception calling "SqlRestore" …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More