PowerShell远程恢复SQL Server数据库遇文件占用问题求助

我使用以下PowerShell脚本在远程SQL Server实例上恢复数据库：

param(
    [Parameter(Mandatory, HelpMessage="Enter the SQL Server hostname. I.e 'plwron164\sql2014'")]
    [string]$sqlServerLocation,
    [Parameter(Mandatory, HelpMessage="Enter the location id custom database name")]
    [string]$locationId,
    [Parameter(Mandatory, HelpMessage="Enter the path to the database backup file. Must be physical path to backup on server!")]
    [string]$backupPath
)
Import-Module .\modules\GenerateEnvironment\gecommon.psd1 -Force
InstallSqlServerModule
$newDbName = "DATABASE`_$locationId"
try {
    RemoveDbIfExists -sqlServerLocation $sqlServerLocation -locationId $locationId
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServerLocation;
    $dbFiles = Invoke-Sqlcmd -ServerInstance $sqlServerLocation -Query "RESTORE FILELISTONLY FROM DISK='$backupPath';" -TrustServerCertificate
    $relocate = @()
    foreach($dbFile in $dbFiles) 
    {
        if($dbFile.Type -eq 'L') 
        {
            $newfile = (Join-Path -Path $server.Settings.DefaultLog -ChildPath $newDbName) + '_log.ldf'
        } 
        else 
        {
            $newfile = (Join-Path -Path $server.Settings.DefaultFile -ChildPath $newDbName) + '.mdf'
        }
        
        $relocate += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ($dbFile.LogicalName,$newfile)
    }

    WriteLog("Trying restore DB in $sqlServerLocation server with database name $newDbName...")
    Restore-SqlDatabase -ServerInstance $sqlServerLocation -Database $newDbName -BackupFile $backupPath -PassThru -RelocateFile $relocate
    WriteLog("Successfully restored DB") 
    WriteLog("Add for test user access to this DB")
    AddPermissionsForUser -sqlServerLocation $sqlServerLocation -locationId $locationId -user "USER_Test"

}
catch {
    WriteErrorLog("Error with restore db. Exception: $_")
    throw
}

执行Restore-SqlDatabase命令时反复出现如下报错：

Restore-SqlDatabase : Microsoft.Data.SqlClient.SqlError: File 'I:\SQL2017\MSSQL14.SQL2017\MSSQL\DATA\DATABASE.mdf' is claimed by 'DATABASE1'(3) and 'DATABASE2'(1). The WITH MOVE clause can be used to relocate one or more files.

手动在SSMS中执行恢复操作可正常完成，需要修改PowerShell脚本实现被占用文件的重定位以解决该问题。

问题根源在于脚本对多数据文件的处理逻辑缺陷：当前代码仅生成一个主数据文件（.mdf）和一个日志文件（.ldf），但备份文件中可能包含多个数据文件（比如次要数据文件.ndf），这些文件的物理路径重复导致冲突报错。

修改后的脚本核心逻辑 #

更新处理dbFiles的循环，为每个数据文件生成唯一的物理文件名，同时增加目标目录存在性检查：

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServerLocation;
$dbFiles = Invoke-Sqlcmd -ServerInstance $sqlServerLocation -Query "RESTORE FILELISTONLY FROM DISK='$backupPath';" -TrustServerCertificate
$relocate = @()

# 确保默认数据和日志目录存在，避免路径缺失错误
if (-not (Test-Path -Path $server.Settings.DefaultFile)) {
    New-Item -Path $server.Settings.DefaultFile -ItemType Directory -Force | Out-Null
}
if (-not (Test-Path -Path $server.Settings.DefaultLog)) {
    New-Item -Path $server.Settings.DefaultLog -ItemType Directory -Force | Out-Null
}

foreach($dbFile in $dbFiles) 
{
    # 提取原始文件的后缀（.mdf/.ndf/.ldf）
    $fileExtension = [System.IO.Path]::GetExtension($dbFile.PhysicalName)
    # 生成唯一文件名：新库名 + 原始逻辑文件名 + 后缀
    $newFileName = "$newDbName`_$($dbFile.LogicalName)$fileExtension"

    if($dbFile.Type -eq 'L') 
    {
        $newfile = Join-Path -Path $server.Settings.DefaultLog -ChildPath $newFileName
    } 
    else 
    {
        $newfile = Join-Path -Path $server.Settings.DefaultFile -ChildPath $newFileName
    }
    
    $relocate += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ($dbFile.LogicalName,$newfile)
}

关键修改说明 #

1. 唯一文件名生成：不再统一使用$newDbName.mdf或$newDbName_log.ldf，而是结合新数据库名、原始逻辑文件名和原始后缀生成唯一路径，确保每个数据文件的物理位置不重复。
2. 保留原始后缀：避免修改文件类型导致SQL Server无法识别数据文件。
3. 目录存在性检查：提前创建缺失的存储目录，消除因路径不存在引发的额外错误。

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