Invoke-ASCMD - Passing values to the XMLA file not working

Question

Post reply

Invoke-ASCMD - Passing values to the XMLA file not working

smiller9270

SSC Veteran

Points: 231
More actions
June 26, 2016 at 4:44 pm

#314668

I need to create a cmd file to backup Analysis Service Database. Because my SQL Server 2014 does not have ascmd.exe, I thought I would use powershell 'Invoke-ASCMD' but I am having problems passing parameters to the XMLA file. Dbname and Backupfile parameters are not recognized in the XMLA file but when I hardcode values in their place, the script runs successfully. Please help!
Command File :
set DBNAME=ANALYSIS_DB
set SCRIPTPATH=E:\cube_backup.xmla
set SRC=ServerName\ServerInstance
set BACKUPPATH=G:\Cube_BackupFolder\
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b)
for /F "tokens=1-3 delims=:. " %%A in ('time/T') do (set var=%%A%%B%%C%)
echo %mydate%_%var%
set BACKUPFILE="%BACKUPPATH%\%DBNAME%_cube_%mydate%_%var%.abf"
powershell.exe -command Invoke-ASCmd -InputFile %SCRIPTPATH% -server %SRC%
--------------------------------------------------------
SCRIPTPATH (XMLA code) :
<Backup
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>$dbname</DatabaseID>
</Object>
<File>$backupfile</File>
<AllowOverwrite>false</AllowOverwrite>
</Backup>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Did you resolve this?

If so, how please?

If not, have you tried giving the values quotation marks?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

blackbinary SSC Veteran Points: 265 More actions · Answer 2

Hi,

I am not very good at the old command line, if the solution can be all in PowerShell, then I might be able to help.

First of all, I am not able to test this in a real environment as I do not have the SSAS installed in my computer and I have never used the Invoke-ASCmd cmdlet, so I will be using the cmdlet as you typed in your post - assuming it is correct, although instead of using the parameter -InputFile, I will use the parameter -Query, based on the description in HERE.

$DBNAME = "ANALYSIS_DB";

$SCRIPTPATH = "E:\cube_backup.xmla";

$SRC = "ServerName\ServerInstance";

$BACKUPPATH = "G:\Cube_BackupFolder";

$date = Get-Date -Format "yyyyMMdd_hhmmtt";

$BACKUPFILE = "$BACKUPPATH\$($DBNAME)_cube_$date.abf";

$query = @"

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object><DatabaseID>$DBNAME</DatabaseID></Object><File>$BACKUPFILE</File>

<AllowOverwrite>false</AllowOverwrite>

</Backup>

"@;

Write-Host "Invoke-ASCmd -Query `"$query`" -Server `"$SRC`"";

Invoke-ASCmd -Query $query -Server $SRC