gpt4 book ai didi

powershell - 使用PowerShell和SMO库从.BAK文件创建新数据库

转载 作者:行者123 更新时间:2023-12-03 01:06:44 25 4
gpt4 key购买 nike

我正在尝试使用powershell从.bak文件创建一个新的SQL Server数据库。

这是我的脚本:

TRY
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
$servername = "MyServer\MyServerInstance"
$datapath= "E:\SQLData\MyNewDataBase"
$logpath= "E:\SQLLogs\MyNewDataBase"
$path= "\\MyServer\BKPFolder\"
$server = new-object("Microsoft.SqlServer.Management.Smo.Server") $servername
$folderitem=Get-ChildItem $path -filter *.bak -rec

foreach($bkfiles in $folderitem)
{
$dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$files = $path+$bkfiles.Name
$backupFile = $files
$dbRestore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$dbRestoreDetails = $dbRestore.ReadBackupHeader($server)
$dbFileList= $dbRestore.ReadFileList($server)

foreach ($row in $dbFileList)
{
$FileType = $row["Type"].ToUpper()

If ($FileType.Equals("D"))
{
$DBLogicalName = $Row["LogicalName"]
}

ELSEIf ($FileType.Equals("L"))
{
$LogLogicalName = $Row["LogicalName"]
}
}

$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

$dbRestoreFile.LogicalFileName = $DBLogicalName
$dbRestoreFile.PhysicalFileName =$datapath + "\" + $DBLogicalName + ".mdf"

$dbRestoreLog.LogicalFileName = $LogLogicalName
$dbRestoreLog.PhysicalFileName = $logpath + "\" + $LogLogicalName + ".ldf"

$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

$dbRestore.Database = "MyNewDataBase"
$dbRestore.NoRecovery = $false
$dbRestore.Action = "DataBase"
$dbRestore.FileNumber = 1;
$dbRestore.ReplaceDatabase = $false;

$dbRestore.SqlRestore($server)
}
} catch {
"Database restore failed:`n`n " + _.Exception.GetBaseException().Message
}

运行此代码时,出现以下错误:

Directory lookup for the file "E:\SQLData\MyNewDataBase\OLDDataBase_Data.mdf" failed with the operating system error 2(The system cannot find the file specified.).

File 'OLDDataBase_Data' cannot be restored to 'E:\SQLData\MyNewDataBase\OLDDataBase_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.



看来我的脚本正在尝试从旧数据库中查找 .mdf.ldf文件。但是我只有旧数据库中的 .bak文件。

我想念什么?

谢谢。

最佳答案

经过大量的研究和尝试,我终于恢复了数据库。
这为我工作:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#Define o novo local dos arquivos de mdf e ldf
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("OldDataBase_Data", "E:\SQLData\MyNewDataBase_DATA.mdf")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("OldDataBase_Log", "E:\SQLLogs\MyNewDataBase_LOG.ldf")

#Executa a restauração
Restore-SqlDatabase -ServerInstance "MyServer\MyServerInstance" -Database "MyNewDataBase" -BackupFile "\\BackupFolder\OldDataBase.bak" -RelocateFile @($RelocateData,$RelocateLog) -NoRecovery
Restore-SqlDatabase -ServerInstance "MyServer\MyServerInstance" -Database "MyNewDataBase" -BackupFile "\\BackupFolder\OldDataBase.trn" -RestoreAction Log -NoRecovery

关于powershell - 使用PowerShell和SMO库从.BAK文件创建新数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47595510/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com