Backup of OLAP Database using Powershell | DBA THINGS

Backup of OLAP Database using Powershell


buy cheap generic finast In this article I would like to explain another method to take the backup of OLAP databases via PowerShell. It will help out to take the backup of multiple OLAP databases with a specified retention period. In Tsql, XMLA function is applied however it is a bit puzzling to initiate the similar process for more than one database.

buy finasteride cheap online uk ###### Configuration ###### # $server_name  $server_name = "<Server Name>" # UNC path of share or on-disk location to which backups will be stored.  $backup_location = "<Full path of Backup location>" # Array of databases that will be backed-up. If $null then all databases # will be backed up.  $user_requested_databases = $null # How long backups will be retained  $retention_period_in_days = 90 ###### End Configuration ###### trap [Exception] {   write-error $("TRAPPED: " + $_.Exception.GetType().FullName)   write-error $("TRAPPED: " + $_.Exception.Message) if ($server) { $server.disconnect() } exit 1 } if ($server_name -eq $null) { $server_name = "localhost" } [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  $server = New-Object Microsoft.AnalysisServices.Server $server.connect($server_name) # Set the directory for backups to the server property # "BackupDir" if it's not otherwise specified  if ($backup_location -eq $null) { $backup_location = ($server.get_ServerProperties() | Where {$_.Name -eq "BackupDir"}).Value}  elseif (!(Test-Path -path $backup_location)) { throw "Specified path ($backup_location) does not exist." } # Generate an array of databases to be backed up $available_databases = ($server.get_Databases() | foreach {$_.Name}) if ($user_requested_databases -eq $null) { $databases = $available_databases} else { $databases = $user_requested_databases.Split(",") # Check that all specified databases actually exist on the server. foreach ($database in $databases) { if ($available_databases -notcontains $database) { throw "$database does not exist on specified server." } } } foreach ($database in ($server.get_Databases() | Where {$databases -contains $_.Name})) { $directory_path = $backup_location + "\" + $database.Name if (!(Test-Path -Path $directory_path)) { New-Item $directory_path -type directory | out-null } [string] $timestamp = date   $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')   $database.Backup("$directory_path\$database-$timestamp.abf") # Cleanup Old Backups Get-ChildItem $directory_path | where {    $_.LastWriteTime -le (Get-Date).AddDays(-$retention_period_in_days) } | remove-item } $server.disconnect()


Amit Kumar

Amit Kumar is a Lead DBA and working with SQL Server Technologies since 2007. Where he focuses on core SQL Server Administration and Development.

%d bloggers like this: