使用powershell连接mssql的相关操作记录
input params
假设我们需要执行的脚本是a.ps1,我们可以进入到对应目录下,在powershell中执行.\a.ps1的命令,其中定义参数代码示例如下:
param( [Parameter(Mandatory=$true)] $localHostName,
$Account="root",$password="123456789")
写在开头,其中Mandatory=$true修饰的参数是必须传入的,其他不传参的情况下使用默认参数。
logFile
定义logFile文件路径:
$logFile = "C:\config.log"
Start-Transcript -path $LogFile -Force -Append –NoClobber(防止被覆盖)
......
Write-Output ('-- log1;')
......
Write-Output ('-- log2;')
......
stop-transcript
连接与执行
连接函数;事务执行;非事务执行;
Function GetSqlConnection([string]$ServerName){
IF ([string]::IsNullOrWhitespace($ServerName)){
$ServerName = [System.Net.Dns]::GetHostName()
}
[string]$ConnectionString = "Data Source=$ServerName;Initial Catalog=$Database;user id=$userName;pwd=$password"
try{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
$SqlConnection.Open()
Write-Host 'Connected to sql server success.'
return $SqlConnection
}
catch{
Write-Warning ('Connect to database failed with error message:{0}' -f,$_) | WriteLog
$SqlConnection.Dispose()
return $null
}
}
function Execute-SqlCommandNonQuery
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string]$Command
)
$cmd = $SqlConnection.CreateCommand()
try
{
$cmd.CommandText = $Command
$cmd.ExecuteNonQuery() | Out-Null
return $true
}
catch [Exception] {
Write-Warning ('Execute Sql command failed with error message:{0}' -f $_)
return $false
}
finally{
$SqlConnection.Close()
}
}
function Execute-SqlCommandsNonQueryWithTran
{
param
(
[System.Data.SqlClient.SqlConnection]$SqlConnection,
[string[]]$Commands
)
$transaction = $SqlConnection.BeginTransaction()
$command = $SqlConnection.CreateCommand()
$command.Transaction = $transaction
try
{
foreach($cmd in $Commands) {
#Write-Host $cmd -ForegroundColor Blue
$command.CommandText = $cmd
$command.ExecuteNonQuery()
}
$transaction.Commit()
return $true
}
catch [Exception] {
$transaction.Rollback()
Write-Warning ('Execute Sql commands failed with error message:{0}' -f $_)
return $false
}
finally{
$SqlConnection.Close()
}
}
T-SQL执行示例:
$tSqls=@("CREATE database dba;"
,-Join("CREATE database ",${dbName})
$conn=GetSqlConnection $dbName
Execute-SqlCommandsNonQuery $conn $tSqls
多线程执行
foreach ($computer in $computers)
{
$ScriptBlock = {
Param (
[string] [Parameter(Mandatory=$true)] $svrInstance
)
enable-SqlAlwaysOn -ServerInstance $svrInstance -Force
}
Start-Job -Name "enablealwayson---$computer" $ScriptBlock -ArgumentList $computer
}
#While loop that will wait until the remaining jobs are finished
for($c=0; $c -lt $computers.Length * 20;) {
if ($(Get-Job -State Completed | Where-Object {$_.Name.Contains("enablealwayson")}).Count -eq $computers.Length){
Remove-job -State Completed
break
}
$c++
Start-Sleep –s 3
if ($c-eq $computers.Length * 20){
throw "error"
}
}
有关powershell多线程执行的操作,可以参考这篇文章:https://www.cnblogs.com/zqj-blog/p/10120743.html