Sql-server – SQL Server connectivity and script execution using PowerShell

powershellsql server

I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = servername; Database = dbname; Integrated Security = True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

But when I use:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server")"SQL01\Instancename"

I am getting an error related with SMO dll. Since I am trying to execute remotely, in this machine SQL Server is not installed so SMO.dll is not available. How can I achieve my aim?

Best Answer

You need to have the accompanying assembly on the machine that you are trying to load on.

If you want to use SMO you have two options:

  1. Install the required files on that machine
  2. Run your script on a box that already has SMO

In order to install SMO without having to install SQL Server, follow the instructions on this BOL reference, Installing SMO.

Related Question