在powershell脚本中执行sql命令

Question

我想通过直接输入命令来执行简单的sql命令，但我想使用Powershell脚本来执行，不想使用powershell API来执行这些命令，而只是调用命令

sqlcmd -S .\SQLEXPRESS -U SA -P mypass
USE [master]
RESTORE DATABASE [easypay] FROM  DISK = N'C:\backup.trn' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER LOGIN [itdb] WITH PASSWORD=N'abc'
GO
USE [easypay]
GO
EXEC sp_change_users_login 'Auto_Fix', 'itdb'
Go
USE [easypay]
GO
sp_changedbowner 'sa'
GO

UPDATE itdb.passwd set password = '1a1dc91c907325c69271ddf0c944bc72'  WHERE itdb.passwd.name = 'abc'
GO

我想要一个powershell脚本，可以直接执行这些命令谢谢

Answer 1

这是我使用的一个脚本，稍微修改了一下。注意:这段代码未经测试：

在Kirill Pashkov的评论之后做了一些修改

$SQLServer = "" #your value
$SQLDBName = "" #your value

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
#Start SQL connection
$SqlCmd.Connection = $SqlConnection

#Create querys
#Query 1
$SqlQuery = "Command;"

#Query 2
$SqlQuery2 = @"
 Command
"@

#Add querys to array
$querys = $SqlQuery, $SqlQuery2

# Run Query
Foreach($query in $querys)
{
    $SqlCmd.CommandText = $query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $OutPut = $SqlAdapter.Fill($DataSet)
    if($OutPut -ne 0){
    Write-Host $OutPut
    }

}
#Close SQL connection
$SqlConnection.Close()