SQLServer PowerShell常用脚本

环境准备

安装SqlServer模块

powershell

# 安装SqlServer模块
Install-Module -Name SqlServer -Scope CurrentUser -Force

# 导入SqlServer模块
Import-Module SqlServer

# 查看已安装的SqlServer模块
Get-Module -Name SqlServer -ListAvailable

连接到SQL Server

powershell

# 使用Windows身份验证连接
$serverInstance = "ServerName\InstanceName"
$database = "DatabaseName"
$conn = Connect-SqlInstance -ServerInstance $serverInstance

# 使用SQL Server身份验证连接
$username = "sa"
$password = ConvertTo-SecureString -String "Password123" -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
$conn = Connect-SqlInstance -ServerInstance $serverInstance -Credential $credential

# 使用连接字符串连接
$connectionString = "Server=ServerName;Database=DatabaseName;Integrated Security=True;"
$conn = Connect-SqlInstance -ConnectionString $connectionString

日常运维脚本

数据库状态检查

生产场景示例：每日检查所有数据库状态，生成报告并发送邮件告警。

powershell

# 检查所有数据库状态
function Check-DatabaseStatus {
    param(
        [string]$ServerInstance
    )
    
    $databases = Get-SqlDatabase -ServerInstance $ServerInstance
    
    $result = @()
    foreach ($db in $databases) {
        $status = [PSCustomObject]@{
            DatabaseName = $db.Name
            Status = $db.Status
            RecoveryModel = $db.RecoveryModel
            SizeMB = [math]::Round($db.Size, 2)
            SpaceAvailableMB = [math]::Round($db.SpaceAvailable, 2)
            LastBackupDate = $db.LastBackupDate
            LastLogBackupDate = $db.LastLogBackupDate
        }
        $result += $status
    }
    
    return $result
}

# 使用示例
Check-DatabaseStatus -ServerInstance "ServerName" | Format-Table -AutoSize

服务状态监控

生产场景示例：监控多台服务器上的SQL Server相关服务，实时告警服务异常。

powershell

# 监控SQL Server相关服务
function Monitor-SqlServices {
    param(
        [string]$ComputerName = $env:COMPUTERNAME
    )
    
    $services = Get-Service -ComputerName $ComputerName | 
                Where-Object {$_.Name -like "*SQL*" -or $_.DisplayName -like "*SQL*"}
    
    $result = @()
    foreach ($service in $services) {
        $status = [PSCustomObject]@{
            ComputerName = $ComputerName
            ServiceName = $service.Name
            DisplayName = $service.DisplayName
            Status = $service.Status
            StartType = $service.StartType
        }
        $result += $status
    }
    
    return $result
}

# 使用示例
Monitor-SqlServices -ComputerName "ServerName" | Format-Table -AutoSize

磁盘空间监控

生产场景示例：监控SQL Server服务器磁盘空间，当使用率超过80%时发送告警邮件。

powershell

# 监控磁盘空间
function Monitor-DiskSpace {
    param(
        [string]$ComputerName = $env:COMPUTERNAME,
        [int]$Threshold = 80
    )
    
    $disks = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $ComputerName -Filter "DriveType = 3"
    
    $result = @()
    foreach ($disk in $disks) {
        $usedPercent = [math]::Round(($disk.Size - $disk.FreeSpace) / $disk.Size * 100, 2)
        $status = [PSCustomObject]@{
            ComputerName = $ComputerName
            DriveLetter = $disk.DeviceID
            VolumeName = $disk.VolumeName
            SizeGB = [math]::Round($disk.Size / 1GB, 2)
            FreeSpaceGB = [math]::Round($disk.FreeSpace / 1GB, 2)
            UsedPercent = $usedPercent
            Status = if ($usedPercent -ge $Threshold) { "Alert" } else { "Normal" }
        }
        $result += $status
    }
    
    return $result
}

# 使用示例
Monitor-DiskSpace -ComputerName "ServerName" -Threshold 80 | Format-Table -AutoSize

备份恢复脚本

自动备份脚本

生产场景示例：根据数据库重要程度，设置不同的备份策略（完整备份、差异备份、日志备份），并自动清理过期备份。

powershell

# 自动备份数据库
function Backup-SqlDatabaseAutomated {
    param(
        [string]$ServerInstance,
        [string]$BackupPath,
        [string]$BackupType = "FULL", # FULL, DIFF, LOG
        [string]$DatabaseName = "*",
        [switch]$CompressBackup = $true
    )
    
    # 确保备份路径存在
    if (-not (Test-Path -Path $BackupPath)) {
        New-Item -Path $BackupPath -ItemType Directory -Force
    }
    
    # 获取要备份的数据库
    if ($DatabaseName -eq "*") {
        $databases = Get-SqlDatabase -ServerInstance $ServerInstance | 
                    Where-Object {$_.Name -notin "master", "model", "msdb", "tempdb"}
    } else {
        $databases = Get-SqlDatabase -ServerInstance $ServerInstance -Name $DatabaseName
    }
    
    $result = @()
    foreach ($db in $databases) {
        # 构建备份文件名
        $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
        $fileExtension = switch ($BackupType) {
            "FULL" { ".bak" }
            "DIFF" { "_Diff.bak" }
            "LOG" { "_Log.trn" }
            default { ".bak" }
        }
        $backupFile = Join-Path -Path $BackupPath -ChildPath "$($db.Name)_$BackupType_$timestamp$fileExtension"
        
        try {
            # 执行备份
            $backupParams = @{
                Database = $db.Name
                ServerInstance = $ServerInstance
                BackupFile = $backupFile
                BackupAction = $BackupType
                CompressBackup = $CompressBackup
            }
            
            Backup-SqlDatabase @backupParams
            
            # 记录成功信息
            $status = [PSCustomObject]@{
                DatabaseName = $db.Name
                BackupType = $BackupType
                BackupFile = $backupFile
                Status = "Success"
                Timestamp = Get-Date
            }
        } catch {
            # 记录失败信息
            $status = [PSCustomObject]@{
                DatabaseName = $db.Name
                BackupType = $BackupType
                BackupFile = $backupFile
                Status = "Failed"
                ErrorMessage = $_.Exception.Message
                Timestamp = Get-Date
            }
        }
        
        $result += $status
    }
    
    return $result
}

# 使用示例
# 完整备份所有用户数据库
Backup-SqlDatabaseAutomated -ServerInstance "ServerName" -BackupPath "D:\Backup" -BackupType "FULL" -CompressBackup

# 事务日志备份指定数据库
Backup-SqlDatabaseAutomated -ServerInstance "ServerName" -BackupPath "D:\Backup" -BackupType "LOG" -DatabaseName "AdventureWorks" -CompressBackup

备份清理脚本

生产场景示例：根据备份类型设置不同的保留期，自动清理过期备份文件，释放磁盘空间。

powershell

# 清理旧备份文件
function Cleanup-OldBackups {
    param(
        [string]$BackupPath,
        [int]$RetentionDays = 7,
        [string]$FilePattern = "*.bak",
        [switch]$Recurse = $false
    )
    
    # 计算保留期限
    $retentionDate = (Get-Date).AddDays(-$RetentionDays)
    
    # 获取要删除的备份文件
    $backupFiles = Get-ChildItem -Path $BackupPath -Filter $FilePattern -Recurse:$Recurse | 
                   Where-Object {$_.LastWriteTime -lt $retentionDate}
    
    $result = @()
    foreach ($file in $backupFiles) {
        try {
            Remove-Item -Path $file.FullName -Force
            $status = [PSCustomObject]@{
                BackupFile = $file.FullName
                SizeMB = [math]::Round($file.Length / 1MB, 2)
                LastWriteTime = $file.LastWriteTime
                Status = "Deleted"
                Timestamp = Get-Date
            }
        } catch {
            $status = [PSCustomObject]@{
                BackupFile = $file.FullName
                SizeMB = [math]::Round($file.Length / 1MB, 2)
                LastWriteTime = $file.LastWriteTime
                Status = "Failed"
                ErrorMessage = $_.Exception.Message
                Timestamp = Get-Date
            }
        }
        
        $result += $status
    }
    
    return $result
}

# 使用示例
# 清理7天前的.bak和.trn文件
Cleanup-OldBackups -BackupPath "D:\Backup" -RetentionDays 7 -FilePattern "*.bak", "*.trn" -Recurse

恢复数据库脚本

生产场景示例：从备份文件恢复数据库到测试环境，用于数据验证或开发测试。

powershell

# 恢复数据库
function Restore-SqlDatabaseFromBackup {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$BackupFile,
        [string]$DataFilePath = $null,
        [string]$LogFilePath = $null,
        [switch]$ReplaceDatabase = $false
    )
    
    try {
        # 获取备份文件信息
        $backupInfo = Restore-SqlDatabase -ServerInstance $ServerInstance -Database $DatabaseName -BackupFile $backupFile -ReplaceDatabase:$ReplaceDatabase -RestoreAction VerifyOnly
        
        # 构建恢复参数
        $restoreParams = @{
            ServerInstance = $ServerInstance
            Database = $DatabaseName
            BackupFile = $backupFile
            ReplaceDatabase = $ReplaceDatabase
        }
        
        # 如果指定了数据文件和日志文件路径，添加移动参数
        if ($DataFilePath -and $LogFilePath) {
            $dataFile = $backupInfo.FileList | Where-Object {$_.Type -eq "D"} | Select-Object -First 1
            $logFile = $backupInfo.FileList | Where-Object {$_.Type -eq "L"} | Select-Object -First 1
            
            $relocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dataFile.LogicalName, $DataFilePath)
            $relocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logFile.LogicalName, $LogFilePath)
            
            $restoreParams.Add("RelocateFile", @($relocateData, $relocateLog))
        }
        
        # 执行恢复
        Restore-SqlDatabase @restoreParams
        
        $status = [PSCustomObject]@{
            DatabaseName = $DatabaseName
            BackupFile = $BackupFile
            Status = "Success"
            Timestamp = Get-Date
        }
    } catch {
        $status = [PSCustomObject]@{
            DatabaseName = $DatabaseName
            BackupFile = $BackupFile
            Status = "Failed"
            ErrorMessage = $_.Exception.Message
            Timestamp = Get-Date
        }
    }
    
    return $status
}

# 使用示例
# 恢复数据库到新位置
Restore-SqlDatabaseFromBackup -ServerInstance "ServerName" -DatabaseName "AdventureWorks_Restored" -BackupFile "D:\Backup\AdventureWorks_FULL_20231227_143000.bak" -DataFilePath "D:\MSSQL\Data\AdventureWorks_Restored.mdf" -LogFilePath "D:\MSSQL\Log\AdventureWorks_Restored.ldf" -ReplaceDatabase

监控脚本

性能监控脚本

生产场景示例：定期收集SQL Server性能指标，生成性能报告，用于趋势分析和性能调优。

powershell

# 监控SQL Server性能指标
function Monitor-SqlPerformance {
    param(
        [string]$ServerInstance,
        [int]$SampleCount = 10,
        [int]$SampleInterval = 5
    )
    
    $result = @()
    
    for ($i = 0; $i -lt $SampleCount; $i++) {
        $timestamp = Get-Date
        
        # 获取性能计数器
        $perfCounters = @(
            "SQLServer:Buffer Manager\Buffer cache hit ratio",
            "SQLServer:Buffer Manager\Page life expectancy",
            "SQLServer:General Statistics\User Connections",
            "SQLServer:SQL Statistics\Batch Requests/sec",
            "SQLServer:Wait Statistics\Lock Wait Time (ms)",
            "Processor(_Total)\% Processor Time",
            "PhysicalDisk(_Total)\Avg. Disk Queue Length"
        )
        
        $countersData = Get-Counter -Counter $perfCounters -SampleInterval $SampleInterval -MaxSamples 1
        
        $performanceData = [PSCustomObject]@{
            Timestamp = $timestamp
        }
        
        foreach ($counter in $countersData.CounterSamples) {
            $counterName = $counter.Path -replace "\\.*\\", ""
            $performanceData | Add-Member -NotePropertyName $counterName -NotePropertyValue $counter.CookedValue
        }
        
        $result += $performanceData
        
        if ($i -lt $SampleCount - 1) {
            Start-Sleep -Seconds $SampleInterval
        }
    }
    
    return $result
}

# 使用示例
# 监控10次，每次间隔5秒
$performanceData = Monitor-SqlPerformance -ServerInstance "ServerName" -SampleCount 10 -SampleInterval 5
$performanceData | Format-Table -AutoSize

# 导出到CSV
$performanceData | Export-Csv -Path "C:\Temp\SqlPerformance.csv" -NoTypeInformation

慢查询监控

生产场景示例：实时监控运行时间超过5秒的查询，及时发现并优化慢查询。

powershell

# 监控慢查询
function Monitor-SlowQueries {
    param(
        [string]$ServerInstance,
        [int]$ThresholdSeconds = 5
    )
    
    $query = @"
    SELECT 
        r.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        r.status,
        r.command,
        r.total_elapsed_time / 1000 AS TotalElapsedTimeSeconds,
        r.cpu_time / 1000 AS CPUTimeSeconds,
        r.reads,
        r.writes,
        r.logical_reads,
        t.text AS SqlText
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE r.total_elapsed_time / 1000 > @ThresholdSeconds
    ORDER BY r.total_elapsed_time DESC
"@
    
    $params = @{
        ThresholdSeconds = $ThresholdSeconds
    }
    
    $result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -Database "master" -Parameter $params
    return $result
}

# 使用示例
# 监控运行时间超过5秒的查询
Monitor-SlowQueries -ServerInstance "ServerName" -ThresholdSeconds 5 | Format-Table -AutoSize

性能优化脚本

索引碎片监控和维护

生产场景示例：根据索引碎片程度，自动选择重建或重组索引，优化查询性能。

powershell

# 监控和维护索引碎片
function Maintain-SqlIndexes {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [float]$FragmentationThresholdLow = 5.0,
        [float]$FragmentationThresholdHigh = 30.0,
        [switch]$RebuildOnline = $true
    )
    
    $result = @()
    
    # 获取数据库
    $db = Get-SqlDatabase -ServerInstance $ServerInstance -Name $DatabaseName
    
    # 获取所有表
    $tables = $db.Tables | Where-Object {$_.IsSystemObject -eq $false}
    
    foreach ($table in $tables) {
        # 获取表的索引
        $indexes = $table.Indexes | Where-Object {$_.IndexKeyType -ne "None"}
        
        foreach ($index in $indexes) {
            # 检查索引碎片
            $fragmentation = $index.EnumFragmentation() | Select-Object -ExpandProperty Fragmentation
            
            if ($fragmentation -ge $FragmentationThresholdLow) {
                $action = if ($fragmentation -lt $FragmentationThresholdHigh) { "Reorganize" } else { "Rebuild" }
                
                try {
                    # 执行索引维护
                    if ($action -eq "Reorganize") {
                        $index.Reorganize()
                    } else {
                        $rebuildParams = @{}
                        if ($RebuildOnline -and $db.CompatibilityLevel -ge 90) {
                            $rebuildParams.Add("Online", $true)
                        }
                        $index.Rebuild(@rebuildParams)
                    }
                    
                    $status = [PSCustomObject]@{
                        DatabaseName = $db.Name
                        TableName = $table.Name
                        IndexName = $index.Name
                        FragmentationBefore = [math]::Round($fragmentation, 2)
                        Action = $action
                        Status = "Success"
                        Timestamp = Get-Date
                    }
                } catch {
                    $status = [PSCustomObject]@{
                        DatabaseName = $db.Name
                        TableName = $table.Name
                        IndexName = $index.Name
                        FragmentationBefore = [math]::Round($fragmentation, 2)
                        Action = $action
                        Status = "Failed"
                        ErrorMessage = $_.Exception.Message
                        Timestamp = Get-Date
                    }
                }
                
                $result += $status
            }
        }
    }
    
    return $result
}

# 使用示例
# 维护索引碎片，重建碎片率>30%的索引，重组碎片率5%-30%的索引
Maintain-SqlIndexes -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -FragmentationThresholdLow 5.0 -FragmentationThresholdHigh 30.0 -RebuildOnline

更新统计信息

生产场景示例：定期更新数据库统计信息，确保查询优化器生成准确的执行计划。

powershell

# 更新统计信息
function Update-SqlStatistics {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$TableName = "*",
        [string]$StatisticsScanType = "FullScan" # FullScan, SampleScan, Resample
    )
    
    $result = @()
    
    # 获取数据库
    $db = Get-SqlDatabase -ServerInstance $ServerInstance -Name $DatabaseName
    
    # 获取要更新统计信息的表
    if ($TableName -eq "*") {
        $tables = $db.Tables | Where-Object {$_.IsSystemObject -eq $false}
    } else {
        $tables = $db.Tables | Where-Object {$_.Name -eq $TableName -and $_.IsSystemObject -eq $false}
    }
    
    foreach ($table in $tables) {
        # 获取表的统计信息
        $statistics = $table.Statistics
        
        foreach ($stat in $statistics) {
            try {
                # 更新统计信息
                $stat.Update($StatisticsScanType)
                
                $status = [PSCustomObject]@{
                    DatabaseName = $db.Name
                    TableName = $table.Name
                    StatisticsName = $stat.Name
                    ScanType = $StatisticsScanType
                    Status = "Success"
                    Timestamp = Get-Date
                }
            } catch {
                $status = [PSCustomObject]@{
                    DatabaseName = $db.Name
                    TableName = $table.Name
                    StatisticsName = $stat.Name
                    ScanType = $StatisticsScanType
                    Status = "Failed"
                    ErrorMessage = $_.Exception.Message
                    Timestamp = Get-Date
                }
            }
            
            $result += $status
        }
    }
    
    return $result
}

# 使用示例
# 更新指定数据库所有表的统计信息
Update-SqlStatistics -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -StatisticsScanType "FullScan"

# 更新指定表的统计信息
Update-SqlStatistics -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -TableName "SalesOrderHeader" -StatisticsScanType "SampleScan"

安全管理脚本

检查用户权限

生产场景示例：定期审计数据库用户权限，确保符合最小权限原则。

powershell

# 检查数据库用户权限
function Check-DatabaseUserPermissions {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName
    )
    
    $query = @"
    SELECT 
        dp.name AS DatabaseUser,
        dp.type_desc AS UserType,
        dp.default_schema_name AS DefaultSchema,
        rp.name AS RoleName,
        rp.type_desc AS RoleType
    FROM sys.database_principals dp
    LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
    LEFT JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id
    WHERE dp.type IN ('S', 'U', 'G') -- SQL 用户、Windows 用户、Windows 组
    ORDER BY dp.name, rp.name
"@
    
    $result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -Query $query
    return $result
}

# 使用示例
Check-DatabaseUserPermissions -ServerInstance "ServerName" -DatabaseName "AdventureWorks" | Format-Table -AutoSize

创建数据库用户和权限

生产场景示例：为新应用创建数据库用户，并分配最小必要权限。

powershell

# 创建数据库用户并分配权限
function New-DatabaseUser {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$UserName,
        [string]$Password = $null,
        [string[]]$Roles = @("db_datareader", "db_datawriter"),
        [string]$DefaultSchema = "dbo"
    )
    
    try {
        # 获取数据库
        $db = Get-SqlDatabase -ServerInstance $ServerInstance -Name $DatabaseName
        
        # 创建登录名（如果使用SQL Server身份验证）
        if ($Password) {
            $server = Get-SqlInstance -ServerInstance $ServerInstance
            $login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $server, $UserName
            $login.LoginType = "SqlLogin"
            $login.PasswordPolicyEnforced = $true
            $login.Password = $Password
            $login.Create()
        }
        
        # 创建数据库用户
        $user = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $db, $UserName
        $user.Login = $UserName
        $user.DefaultSchema = $DefaultSchema
        $user.Create()
        
        # 分配角色
        foreach ($role in $Roles) {
            $user.AddToRole($role)
        }
        
        $status = [PSCustomObject]@{
            DatabaseName = $db.Name
            UserName = $UserName
            Roles = $Roles -join ", "
            DefaultSchema = $DefaultSchema
            Status = "Success"
            Timestamp = Get-Date
        }
    } catch {
        $status = [PSCustomObject]@{
            DatabaseName = $DatabaseName
            UserName = $UserName
            Roles = $Roles -join ", "
            DefaultSchema = $DefaultSchema
            Status = "Failed"
            ErrorMessage = $_.Exception.Message
            Timestamp = Get-Date
        }
    }
    
    return $status
}

# 使用示例
# 创建SQL Server用户并分配读写权限
New-DatabaseUser -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -UserName "TestUser" -Password "Password123" -Roles @("db_datareader", "db_datawriter")

# 创建Windows用户并分配只读权限
New-DatabaseUser -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -UserName "DOMAIN\WindowsUser" -Roles @("db_datareader")

自动化部署脚本

数据库部署脚本

生产场景示例：自动化部署数据库脚本，支持事务处理和错误处理。

powershell

# 部署数据库脚本
function Deploy-SqlScript {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$ScriptPath,
        [switch]$UseTransaction = $true,
        [switch]$StopOnError = $true
    )
    
    try {
        # 读取脚本内容
        $scriptContent = Get-Content -Path $ScriptPath -Raw
        
        # 执行脚本
        $executeParams = @{
            ServerInstance = $ServerInstance
            Database = $DatabaseName
            Query = $scriptContent
            ErrorAction = if ($StopOnError) { "Stop" } else { "Continue" }
        }
        
        if ($UseTransaction) {
            $executeParams.Add("UseTransaction", $true)
        }
        
        $result = Invoke-Sqlcmd @executeParams
        
        $status = [PSCustomObject]@{
            DatabaseName = $DatabaseName
            ScriptPath = $ScriptPath
            Status = "Success"
            RowsAffected = $result.Count
            Timestamp = Get-Date
        }
    } catch {
        $status = [PSCustomObject]@{
            DatabaseName = $DatabaseName
            ScriptPath = $ScriptPath
            Status = "Failed"
            ErrorMessage = $_.Exception.Message
            Timestamp = Get-Date
        }
    }
    
    return $status
}

# 使用示例
# 部署数据库脚本，使用事务，出错停止
Deploy-SqlScript -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -ScriptPath "C:\Scripts\CreateTables.sql" -UseTransaction -StopOnError

# 部署脚本，不使用事务，出错继续
Deploy-SqlScript -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -ScriptPath "C:\Scripts\InsertData.sql" -UseTransaction:$false -StopOnError:$false

批量部署脚本

生产场景示例：按顺序批量部署多个数据库脚本，支持自动排序和错误处理。

powershell

# 批量部署数据库脚本
function Deploy-SqlScriptsBatch {
    param(
        [string]$ServerInstance,
        [string]$DatabaseName,
        [string]$ScriptsPath,
        [string]$FilePattern = "*.sql",
        [switch]$UseTransaction = $true,
        [switch]$StopOnError = $true
    )
    
    # 获取要部署的脚本文件（按名称排序）
    $scriptFiles = Get-ChildItem -Path $ScriptsPath -Filter $FilePattern | Sort-Object -Property Name
    
    $result = @()
    foreach ($scriptFile in $scriptFiles) {
        Write-Host "部署脚本: $($scriptFile.Name)" -ForegroundColor Cyan
        
        $deployResult = Deploy-SqlScript -ServerInstance $ServerInstance -DatabaseName $DatabaseName -ScriptPath $scriptFile.FullName -UseTransaction:$UseTransaction -StopOnError:$StopOnError
        
        $result += $deployResult
        
        if ($deployResult.Status -eq "Failed" -and $StopOnError) {
            Write-Host "脚本部署失败，停止部署" -ForegroundColor Red
            break
        }
    }
    
    return $result
}

# 使用示例
# 批量部署指定目录下的所有.sql脚本
Deploy-SqlScriptsBatch -ServerInstance "ServerName" -DatabaseName "AdventureWorks" -ScriptsPath "C:\Scripts\Database" -FilePattern "*.sql" -UseTransaction -StopOnError

监控告警脚本

发送邮件告警

生产场景示例：当监控指标超过阈值时，自动发送邮件告警给DBA团队。

powershell

# 发送邮件告警
function Send-SqlAlert {
    param(
        [string]$Subject,
        [string]$Body,
        [string[]]$To,
        [string]$From = "alerts@example.com",
        [string]$SmtpServer = "smtp.example.com",
        [int]$SmtpPort = 25,
        [switch]$UseSsl = $false,
        [pscredential]$Credential = $null
    )
    
    $sendMailParams = @{
        From = $From
        To = $To
        Subject = $Subject
        Body = $Body
        SmtpServer = $SmtpServer
        Port = $SmtpPort
        UseSsl = $UseSsl
        ErrorAction = "Stop"
    }
    
    if ($Credential) {
        $sendMailParams.Add("Credential", $Credential)
    }
    
    try {
        Send-MailMessage @sendMailParams
        return $true
    } catch {
        Write-Error "发送邮件失败: $($_.Exception.Message)"
        return $false
    }
}

# 使用示例
# 发送磁盘空间告警
$diskSpace = Monitor-DiskSpace -ComputerName "ServerName" -Threshold 80
$alertDisks = $diskSpace | Where-Object {$_.Status -eq "Alert"}

if ($alertDisks.Count -gt 0) {
    $body = $alertDisks | Format-Table -AutoSize | Out-String
    $subject = "告警：服务器 $($env:COMPUTERNAME) 磁盘空间不足"
    $to = @("dba@example.com", "admin@example.com")
    
    Send-SqlAlert -Subject $subject -Body $body -To $to -SmtpServer "smtp.example.com" -From "alerts@example.com"
}

脚本部署和调度

使用Task Scheduler调度脚本

生产场景示例：创建计划任务，定期执行备份、监控和维护脚本。

powershell

# 创建计划任务执行PowerShell脚本
function New-SqlScriptScheduledTask {
    param(
        [string]$TaskName,
        [string]$ScriptPath,
        [string]$ScriptArguments = "",
        [string]$Schedule = "Daily", # Daily, Weekly, Monthly, Once
        [string]$StartTime = "02:00:00",
        [string]$UserName = "SYSTEM",
        [string]$Password = $null
    )
    
    # 构建PowerShell命令
    $powershellCommand = "-ExecutionPolicy Bypass -File '$ScriptPath' $ScriptArguments"
    
    # 创建任务动作
    $action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument $powershellCommand
    
    # 创建任务触发器
    $trigger = switch ($Schedule) {
        "Daily" { New-ScheduledTaskTrigger -Daily -At $StartTime }
        "Weekly" { New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At $StartTime }
        "Monthly" { New-ScheduledTaskTrigger -Monthly -Days 1 -At $StartTime }
        "Once" { New-ScheduledTaskTrigger -Once -At $StartTime }
        default { New-ScheduledTaskTrigger -Daily -At $StartTime }
    }
    
    # 设置任务主体
    $principalParams = @{
        UserId = $UserName
        LogonType = if ($UserName -eq "SYSTEM") { "ServiceAccount" } else { "Password" }
    }
    
    $principal = New-ScheduledTaskPrincipal @principalParams
    
    # 注册计划任务
    $registerParams = @{
        TaskName = $TaskName
        Action = $action
        Trigger = $trigger
        Principal = $principal
        Description = "SQL Server 自动化脚本任务"
        Force = $true
    }
    
    if ($UserName -ne "SYSTEM" -and $Password) {
        $registerParams.Add("User" , $UserName)
        $registerParams.Add("Password" , $Password)
    }
    
    Register-ScheduledTask @registerParams
    
    Write-Host "计划任务 '$TaskName' 已创建" -ForegroundColor Green
}

# 使用示例
# 创建每日凌晨2点执行的备份脚本任务
New-SqlScriptScheduledTask -TaskName "SQL Server Backup" -ScriptPath "C:\Scripts\Backup-SqlDatabase.ps1" -Schedule "Daily" -StartTime "02:00:00" -UserName "SYSTEM"

版本差异

SQL Server 2012及以前

仅支持Windows PowerShell
需要安装SQL Server管理工具获取PowerShell模块
部分高级功能不可用

SQL Server 2014

引入了SqlServer模块，替换了之前的SQLPS模块
支持更多自动化功能
开始支持PowerShell Core的初步功能

SQL Server 2016

增强了SqlServer模块功能
支持更多DBA常用功能
开始支持跨平台操作

SQL Server 2017

全面支持PowerShell Core 6.0+
增强了跨平台支持
引入了更多性能监控和优化功能

SQL Server 2019

支持PowerShell Core 7.0+
增强了智能查询处理相关的脚本功能
引入了更多安全管理功能

SQL Server 2022

最新的SqlServer模块支持
增强了大数据集群相关的脚本功能
引入了更多云集成功能

最佳实践

脚本编写最佳实践

使用函数封装：将功能封装为函数，提高复用性和可维护性
添加参数验证：验证输入参数的有效性
错误处理：使用try-catch块处理异常
日志记录：记录脚本执行结果和错误信息
注释清晰：添加详细的注释说明脚本功能和参数
测试脚本：在测试环境中充分测试脚本
使用版本控制：将脚本存储在版本控制系统中
定期更新：根据SQL Server版本和需求变化更新脚本

安全最佳实践

最小权限原则：使用具有最小必要权限的账户运行脚本
加密敏感信息：不要在脚本中硬编码密码，使用安全字符串或凭据管理
限制脚本访问权限：设置适当的文件系统权限保护脚本
审计脚本执行：记录脚本执行情况，便于审计
使用SSL加密连接：连接SQL Server时使用SSL加密

性能最佳实践

批量处理：处理大量数据时使用批量操作
减少网络往返：减少与SQL Server的连接次数
使用适当的索引：在查询中使用适当的索引
优化查询：编写高效的T-SQL查询
监控脚本性能：监控脚本执行时间和资源使用

常见问题（FAQ）

如何解决"无法加载文件 ... 因为在此系统上禁止运行脚本"错误？

解答：

powershell

# 查看当前执行策略
Get-ExecutionPolicy

# 设置执行策略为RemoteSigned（允许本地脚本执行）
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

# 或设置为Unrestricted（允许所有脚本执行，不推荐在生产环境使用）
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser

如何安装旧版本的SqlServer模块？

解答：

powershell

# 查看可用的SqlServer模块版本
Find-Module -Name SqlServer -AllVersions | Select-Object -Property Name, Version | Sort-Object -Property Version -Descending

# 安装特定版本的SqlServer模块
Install-Module -Name SqlServer -RequiredVersion 21.1.18256 -Scope CurrentUser -Force

如何处理长运行时间的脚本？

解答：

优化T-SQL查询：提高查询效率
使用批量操作：将大任务拆分为多个小批量操作
并行处理：使用PowerShell的并行功能处理多个任务
增加超时时间：在调用SqlServer cmdlet时增加超时时间
监控脚本执行：使用Start-Transcript记录脚本执行过程

如何调试PowerShell脚本？

解答：

使用Write-Host或Write-Verbose输出调试信息
使用Start-Transcript记录脚本执行过程
在PowerShell ISE或VS Code中设置断点调试
使用Get-History和Invoke-History查看和重复执行命令
使用Trace-Command跟踪PowerShell命令执行

如何在Linux或macOS上运行SQL Server PowerShell脚本？

解答：

安装PowerShell Core：https://docs.microsoft.com/powershell/scripting/install/installing-powershell
安装SqlServer模块：Install-Module -Name SqlServer -Scope CurrentUser -Force
运行脚本：pwsh -File script.ps1

注意：某些Windows特定的功能（如Task Scheduler）在Linux和macOS上不可用。

如何在脚本中安全地存储密码？

解答：

powershell

# 生成安全字符串
$password = Read-Host -AsSecureString
$password | ConvertFrom-SecureString | Out-File "C:\Scripts\password.txt"

# 读取安全字符串
$securePassword = Get-Content "C:\Scripts\password.txt" | ConvertTo-SecureString
$credential = New-Object System.Management.Automation.PSCredential("username", $securePassword)

如何处理SqlServer模块与其他模块的冲突？

解答：

powershell

# 查看模块冲突
Get-Module -ListAvailable | Where-Object {$_.Name -eq "SqlServer" -or $_.Name -eq "SQLPS"}

# 卸载冲突模块
Remove-Module -Name SQLPS -Force

# 导入特定版本的SqlServer模块
Import-Module -Name SqlServer -RequiredVersion 21.1.18256 -Force

如何实现脚本的自动重试机制？

解答：

powershell

# 带有重试机制的函数示例
function Invoke-SqlWithRetry {
    param(
        [string]$ServerInstance,
        [string]$Database,
        [string]$Query,
        [int]$MaxRetries = 3,
        [int]$RetryIntervalSeconds = 5
    )
    
    $retryCount = 0
    $success = $false
    
    while (-not $success -and $retryCount -lt $MaxRetries) {
        try {
            $result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
            $success = $true
            return $result
        } catch {
            $retryCount++
            if ($retryCount -lt $MaxRetries) {
                Write-Warning "执行失败，$RetryIntervalSeconds秒后重试...（$retryCount/$MaxRetries）"
                Start-Sleep -Seconds $RetryIntervalSeconds
            } else {
                Write-Error "执行失败，已达到最大重试次数"
                throw
            }
        }
    }
}

总结

本文介绍了SQL Server管理中常用的PowerShell脚本，涵盖了日常运维、备份恢复、性能监控、安全管理、自动化部署等方面。这些脚本可以帮助DBA提高工作效率，实现自动化管理，减少人为错误。

在实际使用中，建议根据环境和需求对脚本进行适当修改和扩展，确保脚本的安全性、可靠性和性能。同时，定期更新和维护脚本，以适应SQL Server版本和业务需求的变化。

SQLServer PowerShell常用脚本 ​

环境准备 ​

安装SqlServer模块 ​

连接到SQL Server ​

日常运维脚本 ​

数据库状态检查 ​

服务状态监控 ​

磁盘空间监控 ​

备份恢复脚本 ​

自动备份脚本 ​

备份清理脚本 ​

恢复数据库脚本 ​

监控脚本 ​

性能监控脚本 ​

慢查询监控 ​

性能优化脚本 ​

索引碎片监控和维护 ​

更新统计信息 ​

安全管理脚本 ​

检查用户权限 ​

创建数据库用户和权限 ​

自动化部署脚本 ​

数据库部署脚本 ​

批量部署脚本 ​

监控告警脚本 ​

发送邮件告警 ​

脚本部署和调度 ​

使用Task Scheduler调度脚本 ​

版本差异 ​

SQL Server 2012及以前 ​

SQL Server 2014 ​

SQL Server 2016 ​

SQL Server 2017 ​

SQL Server 2019 ​

SQL Server 2022 ​

最佳实践 ​

脚本编写最佳实践 ​

安全最佳实践 ​

性能最佳实践 ​

常见问题（FAQ） ​

如何解决"无法加载文件 ... 因为在此系统上禁止运行脚本"错误？ ​

如何安装旧版本的SqlServer模块？ ​

如何处理长运行时间的脚本？ ​

如何调试PowerShell脚本？ ​

如何在Linux或macOS上运行SQL Server PowerShell脚本？ ​

如何在脚本中安全地存储密码？ ​

如何处理SqlServer模块与其他模块的冲突？ ​

如何实现脚本的自动重试机制？ ​

总结 ​

SQLServer PowerShell常用脚本

环境准备

安装SqlServer模块

连接到SQL Server

日常运维脚本

数据库状态检查

服务状态监控

磁盘空间监控

备份恢复脚本

自动备份脚本

备份清理脚本

恢复数据库脚本

监控脚本

性能监控脚本

慢查询监控

性能优化脚本

索引碎片监控和维护

更新统计信息

安全管理脚本

检查用户权限

创建数据库用户和权限

自动化部署脚本

数据库部署脚本

批量部署脚本

监控告警脚本

发送邮件告警

脚本部署和调度

使用Task Scheduler调度脚本

版本差异

SQL Server 2012及以前

SQL Server 2014

SQL Server 2016

SQL Server 2017

SQL Server 2019

SQL Server 2022

最佳实践

脚本编写最佳实践

安全最佳实践

性能最佳实践

常见问题（FAQ）

如何解决"无法加载文件 ... 因为在此系统上禁止运行脚本"错误？

如何安装旧版本的SqlServer模块？

如何处理长运行时间的脚本？

如何调试PowerShell脚本？

如何在Linux或macOS上运行SQL Server PowerShell脚本？

如何在脚本中安全地存储密码？

如何处理SqlServer模块与其他模块的冲突？

如何实现脚本的自动重试机制？

总结