Backup SQL Server SSAS cube Agent and dynamic name. Hi all,
I have not write any post on this blog for reasons that do not come to the story, but today I return with a new post that I find interesting.
Yesterday I got the need to schedule a backup of both a SQL Server database (transactional) and cubes that are in pre-production server with SQL Server Agent. I'm not going to roll with it because there's so much information on Google but I do want to dwell on something that a priori does not seem easy. Backing up of cubes with dynamic name, ie cubo_20110225.
Make a back of a cube is fairly simple:
http://www.microsoft.com/latam/technet/productos/servers/sql/2005/bkupssas.mspx But what if we want the name of our backup includes the date and do not crush the previous? Searching the internet
not see anything that was intuitive so it is best to get down to work and although I have no idea of \u200b\u200bPowerShell pajolera saw a script that could serve:
http://powershell.com/ cs/media/p/47.aspx
As in SQL Server Agent can create a Job Step Type PowerShell thought I was on the right path and making modifications to the code in an intuitive way I run something like what is shown below and it works perfectly.
sure can be done better and more elegant (and I say that my knowledge PowerShell are 0 ranker), but to get by to serve.
$ ServerInstance = "localhost"
$ backupDestination = "D: \\ bkup \\ automatically \\"
$ logdir = "" # Load
Microsoft Analysis Services assembly, output error messages to null
[System.Reflection . Assembly]:: LoadWithPartialName (Microsoft.AnalysisServices ")
# Connect to Analysis Server with specified instance
$SSASserver.Connect($ServerInstance)
# Set Backup destination to Analysis Server default if not supplied
# TIP: using PowerShell "equal" operator
if ($backupDestination -eq "")
{
#Write-Debug "Setting the Destination parameter to the BackupDir parameter"
$BackupDestination = $SSASserver.ServerProperties.Item("BackupDir").Value
}
# Test for existence of Backup Destination path
# TIP: using PowerShell ! operator is equivalent to "-not" operator, see below
if (!(test-path $backupDestination))
{
#Write-Host Destination path `"$backupDestination`" does not exists. Exiting script.
exit 1
}
else
{
#Write-Host Backup files will be written to `"$backupDestination`"
}
# Set Log directory to Analysis Server default if not applied
if ($logDir -eq "")
{
# Write-Debug "Setting the Log directory parameter to the LogDir parameter"
$logDir = $SSASserver.ServerProperties.Item("LogDir").Value
}
# Test for existence of Log directory path
if (!(test-path $logDir))
{
# Write-Debug "djfdaklfjalfjañskdlf"
#Write-Host Log directory `"$logDir`" does not exists. Exiting script.
exit 1
}
else
{
# Write-host Logs will be written to $logDir
}
# Test if Log directory and Backup destination paths end on "\" and add if missing
# TIP: using PowerShell "+=" operator to do a quick string append operation
if (-not $logDir.EndsWith("\"))
{
$logDir += "\"
}
if (-not $backupDestination.EndsWith("\"))
{
$backupDestination += "\"
}
# Create Log file name using Server instance
[string]$logFile = $logDir + "SSASBackup." + $serverInstance.Replace("\","_") + ".log"
# Write-Debug "Log file name is $logFile"
# Write-Debug "Creating database object and set options..."
$dbs = $SSASserver.Databases
$serverBackup.AllowOverwrite = 1
$serverBackup.ApplyCompression = 1
$serverBackup.BackupRemotePartitions = 1
# Create backup timestamp
# TIP: using PowerShell Get-Date to format a datetime string
[string]$backupTS = Get-Date -Format "yyyy-MM-ddTHHmm"
# Add message to backup Log file
# TIP: using PowerShell to output strings to a file
# Write-Debug "Backing up files on $serverInstance at $backupTS"
"Backing up files on $ServerInstance at $backupTS" $db.name + "." + $backupTS + ".abf"
# TIP: using mixed string literals and variable in a Write-Host command
# Write-Host Backing up $db.Name to $serverBackup.File
$db.Backup($serverBackup)
if ($?) {"Successfully backed up " + $db.Name + " to " + $serverBackup.File files backed up to the Log file
# Write-Host Clearing out old files from $BackupDestination
#[int]$retentionHours = $retentionDays * 24 * - 1
#"Deleting old backup files" + "*. Abf) Build Scripts
Giving
is important to select Script with Data and if you want to generate the [dbo] in front of the tables do not forget False marking a "Schema object names Qualify." And this is the result
Easy, right? With the help of this blog: http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting -data-from-one-table-to-another-table /
Salu2 
R 
