Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using ASCMD to run command line scripts for SQL Server Analysis Services


By:   |   Last Updated: 2012-06-08   |   Comments (4)   |   Related Tips: > Analysis Services Administration

Problem

Sometimes it would be helpful to run scripts from a command line for Analysis Services. This would be useful for things like creating backups, processing data or running other tasks. Is there a command line tool like sqlcmd for multidimensional databases and Data Mining?

Solution

ASCMD is the command line tool used not only for Multidimensional databases, but also for Data Mining projects. You can run MDX, DMX and XMLA scripts using this tool. In this example I will show how to backup a Multidimensional Database created in Analysis Services using ascmd.

Requirements

ASCMD is not installed by default. You can obtain a copy of this tool from here.

For this tip, I am assuming that you already have a multidimensional database created.

Getting Started

In this example, we are going to create an Analysis Services Database Backup using ascmd. For this purpose, let's create the backup script first using SQL Server Management Studio.

  1. Open SQL Server Management Studio and start an Analysis Services Project.
     
    Open the SQL Server Management Studio

  2. Right click on the database that you want to backup and select the Back Up... option.

    Backup the Multidimentional Database

  3. Specify the path where you want to store the backup. In this example we will store the backup on the "C:\" drive and the backup file will be  named "Analysis Services Tutorial.abf". In this example, I removed the "Encrypt backup file" option.
     
    Specify the backup path and options

  4. Click on the Script option and select "Script Action To New Query Window" and close the Backup Database window. The script option generates the backup code using XMLA. XMLA is a XML extension used in Analysis Services.
     
    Script Action to New Query Window

  5. Now, we are going to save the script. Go to File > Save XMLAQuery1.xmla As...
     
    Save the script in a different path.

  6. Specify the path of the script and the file name. In this example the path will be the "C:\" drive and the name will be "backupMD.xmla".
     
    Save the file as

  7. We are almost finished. Now to run the command line, go to the path where you copied and pasted the ascmd.exe file. In this example, I copied it to the C:\Program Files\Microsoft SQL Server\100\Tools\Binn folder.

  8. Run the following command:
     
    ascmd -S -i c:\backupMD.xmla

    Use the ascmd

    Command options:

    • ASCMD is the command
    • -S is used to specify the SQL Server Name (if not specified uses the local server)
    • -i is used to specify the input file, in this case we are running the "backupMD.xmla" script.

    To verify that the backup was created, check to see that the "Analysis Services Tutorial.abf" file was created on the C:\ drive.

Next Steps


Last Updated: 2012-06-08


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 23, 2018 - 5:51:31 AM - Pooja Sabat Back To Top

 

 

Hi,

I want to restore the SSAS database using the command prompt. I am using SQL Server 2012. Could you help me on this ?

Thanks,

Pooja Sabat


Thursday, September 18, 2014 - 1:43:09 PM - craig Back To Top

Thanks for posting this, I tried following sample xmla scripts and kept getting typos and syntax issues so this was a great way to circumvent the headache!


Thursday, January 03, 2013 - 1:21:23 PM - CK Back To Top

1.      You can go to here to find more info on how to build/create the ascmd.exe file. However, this is a typo in the document. In the section "Where you can find it?" in step 1, ...Open a Microsoft Visual Studio 2008 command prompt. Click Start, point to All Programs, point to Microsoft SQL Server 2008...should be …Open a Microsoft Visual Studio 2008 command prompt. Click Start, point to All Programs, point to MicrosoftVisual Studio2008...

http://blogs.microsoft.co.il/blogs/bilive/archive/2009/04/15/the-ascmd-command-line-utility-ascmd-exe.aspx

2.      For Step 8 in the tip above on "Run the following command: ascmd -S -i c:\backupMD.xmla". It appears to me that a dot is missing between -S and -i. The screenshot is fine. It worked for me when I used ascmd -S .-i c:\backupMD.xmla

 


Tuesday, June 12, 2012 - 9:18:55 PM - Bill Ramos Back To Top

I was looking for a solution like ASCMD for SQL Server 2012 and discovered that Analysis Services has released cmdlets for PowerShell to do much more than what ASCMD does. The Invoke-ASCCmd cmdlet does the job. To learn more about using Analysis Services with PowerShell, check out http://technet.microsoft.com/en-us/library/hh213141


Learn more about SQL Server tools