批量还原数据库

admin

[AppleScript] 纯文本查看 复制代码

# SQL Server 实例名称
$serverName = "换成你主机的名字"
# 备份文件目录
$backupFolder = "数据库存放的目录"

# 默认的数据和日志文件路径（请根据您的实际情况替换这些路径）
$defaultDataPath = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA"
$defaultLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log"

# 获取备份文件列表
$backupFiles = Get-ChildItem -Path $backupFolder -Filter "*.bak"

# 遍历备份文件并构建还原命令
foreach ($file in $backupFiles) {
    $dbName = $file.BaseName
    $backupFileFullPath = $file.FullName

    # 获取逻辑文件名
    $fileList = Invoke-Sqlcmd -ServerInstance $serverName -Query "RESTORE FILELISTONLY FROM DISK = N'$backupFileFullPath'"
    $mdfLogicalName = $fileList | Where-Object { $_.Type -eq "D" } | Select-Object -ExpandProperty LogicalName
    $ldfLogicalName = $fileList | Where-Object { $_.Type -eq "L" } | Select-Object -ExpandProperty LogicalName

    # 构建还原数据库的 T-SQL 命令
    $restoreCommand = @"
RESTORE DATABASE [$dbName]
FROM DISK = N'$backupFileFullPath'
WITH FILE = 1, 
MOVE N'$mdfLogicalName' TO N'$defaultDataPath\$dbName.mdf',
MOVE N'$ldfLogicalName' TO N'$defaultLogPath\$dbName.ldf',
NOUNLOAD, REPLACE, STATS = 10
"@

    # 执行还原命令
    Invoke-Sqlcmd -ServerInstance $serverName -Query $restoreCommand
    Write-Host "已还原数据库：$dbName"
}

Write-Host "所有备份文件还原完成。"