Azure PowerShell on MacOS - Run Script File

Question

I'm using PowerShell 6.2 on MacOS. I'm trying to run a *.sql file on a target database, but I can't seem to find the appropriate module.

My goal is to copy production database into a sandbox copy - then wipe-out the logins/permissions and set a new. I'm using:

New-AzSqlDatabaseCopy -ResourceGroupName "ProdRG" -ServerName "ProdSrv" -DatabaseName "ProdDB" -CopyResourceGroupName "SandRG" -CopyServerName "SandServ" -CopyDatabaseName "SandDB"

How can I execute a SQL script on the new instance?

Answer 1

For your Local Instance

You can import the SQL module sqlps into your current context by using:

Import-Module "sqlps" -DisableNameChecking

The -DisableNameChecking parameter is to ignore warnings that you might get for importing a module without the 'approved' noun-verb naming scheme that PowerShell recommends from the library.

Then you can run:

Invoke-Sqlcmd -ServerInstance ServerName -inputFile "yoursqlfile.sql" -Database "your database"

To run the sql script against that particular database.

Reference: https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

For your Azure Instance in Azure Powershell:

You can run the following (Found on a SO answer linked below):

$connectionString = "Data Source=MyDataSource;Initial Catalog=MyDB;User ID=user1;Password=pass1;Connection Timeout=90"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = [IO.File]::ReadAllText("C:\...\TestSQL.sql")
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

Refrence: Use Azure Powershell to execute a .sql file

• Niranjan Dharmarajan: Only one thing, that is -ServerInstance not serverName. Thanks
• Peter Kay: @NiranjanDharmarajan thanks for the correction! Edited.