使用Powershell更改SQL Server的实例级别排序规则 [英] Change instance level collation of SQL Server using powershell

问题描述

我想使用Powershell脚本以编程方式更改SQL Server实例的排序规则。以下是手动步骤：

I want to change the collation of SQL Server instance programmatically using powershell script. Followings are the manual steps:

1. 停止SQL Server实例


2. 转到目录位置： C：\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Binn


3. 执行以下命令：sqlservr -c -m -T4022 -T3659 -s SQL2017 -q SQL_Latin1_General_CP1_CI_AS


4. 执行上述命令后，显示以下消息：默认排序规则已成功更改。


5. 然后，我需要按ctrl + c停止进一步执行。我该如何以编程方式执行
   ？

1. Stop the SQL Server instance
2. Go to directory location: "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Binn"
3. Execute following command: sqlservr -c -m -T4022 -T3659 -s"SQL2017" -q"SQL_Latin1_General_CP1_CI_AS"
4. After the execution of the above command, following message displayed: "The default collation was successfully changed."
5. Then I need to press ctrl+c to stop further execution. How can I do this programmatically?

推荐答案

更改SQL Server排序规则，它将执行详细信息记录在事件查看器应用程序日志中。使用循环，我们可以连续检查事件查看器应用程序日志中是否存在SqlServr.exe，当它生成以下日志消息：默认排序规则已成功更改时，我们可以终止该进程。

When we execute the command to change the SQL Server Collation, it logs the execution details in event viewer application logs. Using loop we can check the event viewer application logs for SqlServr.exe continuously, and when it generates the following log message: "The default collation was successfully changed", we can kill the process.

#Take the time stamp before execution of Collation Change Command
$StartDateTime=(Get-Date).AddMinutes(-1)

# Execute the Collation Change Process
Write-Host "Executing SQL Server Collation Change Command"
$CollationChangeProcess=Start-Process -FilePath $SQLRootDirectory -ArgumentList 
"-c -m -T 4022 -T 3659 -s $JustServerInstanceName -q $NewCollationName" - 
NoNewWindow -passthru

Do
{
  $log=Get-WinEvent -FilterHashtable @{logname='application'; 
  providername=$SQLServiceName; starttime = $StartDateTime} | Where-Object - 
  Property Message -Match 'The default collation was successfully changed.'
  IF($log.count -gt 0 -and  $log.TimeCreated -gt $StartDateTime )
  {
    Stop-Process -ID $CollationChangeProcess.ID
    write-host 'Collation Change Process Completed Successfully.'
    break
  }
  $DateTimeNow=(Get-Date)
  $Duration=$DateTimeNow-$StartDateTime
  write-host  $Duration.totalminutes
  Start-Sleep -Seconds 2
  IF ($Duration.totalminutes -gt 2)
  {
    write-host 'Collation Change Process Failed.'
    break
  }
 }while (1 -eq 1)

这篇关于使用Powershell更改SQL Server的实例级别排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！