使用PowerShell从Blob存储恢复SQL数据库时遇独占访问错误求助

问题背景 #

编写了自动化脚本从Azure Blob存储恢复备份至本地SQL Server，但当数据库存在未关闭的连接时，脚本报错：Exclusive access could not be obtained because the database is in use。尝试使用ALTER DATABASE [Warehouse] SET OFFLINE WITH ROLLBACK IMMEDIATE解决无效，需要修改脚本避免该问题。

有效修改方案 #

方案1：切换单用户模式强制断连（推荐） #

相比直接设置数据库离线，切换到单用户模式并立即回滚所有事务，能更可靠地释放数据库的独占访问权，恢复完成后再切回多用户模式。

方案2：主动杀死所有目标数据库连接 #

如果单用户模式仍无法断连，可以先主动查询并终止所有连接到目标数据库的进程，确保恢复操作无干扰。

方案3：添加错误处理与状态检查 #

在关键操作前检查数据库是否存在，避免无效指令报错；用try/catch块捕获异常，精准发送错误通知。

修改后的完整脚本 #

Import-Module SqlServer

# 定义核心配置参数
$serverInstance = "SQL1"
$dbName = "Warehouse"
$backupBlobUrl = "https://xxxxxx.blob.core.windows.net/backup/Warehouse.bak"
$dataFileTargetPath = "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Warehouse.mdf"
$logFileTargetPath = "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Warehouse.ldf"

# 初始化文件重定位对象
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbName, $dataFileTargetPath)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$dbName`_Log", $logFileTargetPath)

try {
    # 检查目标数据库是否存在
    $dbExistsResult = Invoke-Sqlcmd -ServerInstance $serverInstance -Database "Master" `
        -Query "SELECT COUNT(*) AS DbExists FROM sys.databases WHERE name = N'$dbName'" `
        -OutputAs DataTables
    $dbExists = $dbExistsResult.Rows[0].DbExists -eq 1

    if ($dbExists) {
        # 可选：彻底杀死所有连接到目标数据库的进程（若单用户模式仍有问题则启用）
        # Invoke-Sqlcmd -ServerInstance $serverInstance -Database "Master" -Query @"
        # DECLARE @killCmd VARCHAR(8000) = '';
        # SELECT @killCmd += 'KILL ' + CONVERT(VARCHAR(5), session_id) + ';'
        # FROM sys.dm_exec_sessions WHERE database_id = DB_ID(N'$dbName');
        # EXEC(@killCmd);
        # "@

        # 将数据库切换为单用户模式，强制回滚所有事务并断开连接
        Invoke-Sqlcmd -ServerInstance $serverInstance -Database "Master" `
            -Query "ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
        Write-Host "已强制断开[$dbName]的所有连接，切换为单用户模式"
    }

    # 从Azure Blob执行数据库恢复
    Restore-SqlDatabase -ServerInstance $serverInstance -Database $dbName `
        -BackupFile $backupBlobUrl -RelocateFile @($RelocateData, $RelocateLog) -ReplaceDatabase
    Write-Host "[$dbName]数据库恢复完成"

    # 恢复完成后切回多用户模式（仅当原数据库存在时执行）
    if ($dbExists) {
        Invoke-Sqlcmd -ServerInstance $serverInstance -Database "Master" `
            -Query "ALTER DATABASE [$dbName] SET MULTI_USER"
        Write-Host "已将[$dbName]切换回多用户模式"
    }
}
catch {
    # 捕获异常并发送错误邮件
    $errorDetails = "错误时间：$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')`n错误信息：$($_.Exception.Message)`n堆栈跟踪：$($_.ScriptStackTrace)"
    Send-MailMessage -To "xxxx@xxx" -From "xxx@xx" `
        -Subject "Warehouse数据库恢复任务失败" -Body $errorDetails `
        -SmtpServer "xxxxxxxxxxxx" -Port "25"
    # 重新抛出异常，便于后续排查
    throw
}

修改说明 #

1. 单用户模式切换：用ALTER DATABASE [Warehouse] SET SINGLE_USER WITH ROLLBACK IMMEDIATE替代原有的离线设置，确保所有连接被强制断开，恢复操作能获得独占访问权。
2. 可选进程查杀：注释掉的代码可在单用户模式仍无法断连时启用，彻底清理所有目标数据库的活动进程。
3. 异常处理优化：通过try/catch块捕获所有异常，错误邮件包含时间、详情和堆栈跟踪，更利于问题排查。
4. 状态检查：先判断数据库是否存在，避免对不存在的数据库执行无效的ALTER指令。

内容的提问来源于stack exchange，提问作者Philip