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

By: Daniel Calbimonte   |   Updated: 2012-06-08   |   Comments (4)   |   Related: > 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.
    
   
   
   
2. Right click on the database that you want to backup and select the Back Up... option.
   
   
   
   
   
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.
    
   
   
   
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.
    
   
   
   
5. Now, we are going to save the script. Go to File > Save XMLAQuery1.xmla As...
    
   
   
   
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".
    
   
   
   
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

   
   
   

   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

• You can run whatever you want with ascmd; run queries, create Data Mining Structures, predict information with Data Mining. The future is yours and the limits depend on your imagination.
• Further reading
  • More information about ASCMD: http://msdn.microsoft.com/en-us/library/ms365187(v=sql.90).aspx
  • Backup Database Dialog: http://msdn.microsoft.com/en-us/library/ms174582.aspx
  • Backup Element XMLA http://msdn.microsoft.com/en-us/library/ms186622.aspx

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips