想用powershell撰寫SSAS多個資料庫的備份及還原到備援機。
詢問https://chat.openai.com 大概產出了85%的script，剩下的就自已依想要的需求再調整，在工作上很有幫助。
借助一點科技力量再加上自已七拼八湊土法練鋼後，備份及還原大致可以滿足目前的需要，踏出成功的第一步。
備份：
$srv="10.10.1.100" #要備份的主機
$tomail="myemail@mail.com" #通知對象EMAIL
$psfolder="D:\Schedule" #powershell目錄
$abffolder="E:\OLAP\Backup" #備份的主機目錄
$dblist=@("BIDB1","BIDB2","BIDB13","BIDB4")
$body= -join ("執行poweshell目錄：$psfolder\backup_asdb.ps1", "`n")
$body= -join ($body,"開始時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n","Server：",$srv , "`n")
$body= -join ($body,"========================================================`n")
foreach($db in $dblist)
{
try
{
$body= -join ($body,"Backup database $db.....to $abffolder .....")
Backup-ASDatabase "$abffolder\$db.abf" $db -ApplyCompression -AllowOverwrite -Server $srv
if ($LASTEXITCODE -eq 0)
{
$body= -join ($body,"successed.`n")
}
else
{
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
catch {
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
$body= -join ($body,"========================================================`n")
$body = -join($body,"結束時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n")
#write-host $body
Send-MailMessage -Encoding "utf8" -SmtpServer "urmailServer" -From '管理員<myadm@mail.com>' -To "$tomail" -Subject "($srv)每月備份AS資料庫處理結果通知...." -Body $body
還原：
## Set the variables
$serverName = "10.10.1.102" # Replace with your server name and instance name
$backupFolder = "\\10.10.1.100\OLAP\Backup" # Replace with your backup folder path
$databases = @("BIDB1","BIDB2","BIDB13","BIDB4") # Replace with your database names
$tomail="myemail@mail.com" #email to
$psfolder="D:\Schedule\powershell"
$body= -join ("執行poweshell目錄：$psfolder\resretor_asdb.ps1", "`n")
$body= -join ($body,"開始時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n","Server：",$serverName, "`n")
$body= -join ($body,"========================================================`n")
# Restore the databases
foreach ($database in $databases) {
try {
$backupFile = Join-Path $backupFolder "$database.abf"
$body= -join ($body,"Restore database $database.....from $backupFile .....")
if (Test-Path $backupFile) #check backup file exist
{
Restore-ASDatabase -Server $serverName -Name $database -RestoreFile $backupFile -AllowOverwrite
$body= -join ($body," successfully.`n")
}
else
{
$body= -join ($body," failed!! File not exist!! `n")
}
#變更連線字串(因為要將備援機的連線切到唯讀的always on)
$xmla2 = @"
<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>$database</DatabaseID>
<DataSourceID>$database</DataSourceID>
</Object>
<ObjectDefinition>
<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xsi:type="RelationalDataSource">
<ID>$database</ID>
<Name>$database</Name>
<ConnectionString>Provider=SQLNCLI11.1;Data Source=$urserver;Persist Security Info=True;User ID=urname;Password=urpwd;Initial Catalog=$database</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
</ObjectDefinition>
</Alter>
"@
# Execute the XMLA command using Invoke-ASCmd
Invoke-ASCmd -Server $asServer -Database $database -Query $xmla2
if ($LASTEXITCODE -eq 0)
{
$body= -join ($body,"alert db ($database) connection string successfully.`n" )
}else {
$body= -join ($body,"alert db ($database) connection string failed!!. Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
catch
{
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
$body= -join ($body,"========================================================`n")
$body= -join($body,"結束時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n")
write-host $body
Send-MailMessage -Encoding "utf8" -SmtpServer "urmailServer" -From '管理員<myadm@mail.com>' -To "$tomail" -Subject "($serverName)每月還原SSAS DB處理結果通知...." -Body $body
