Using ASCMD to run command line scripts for SQL Server Analysis Services
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?
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.
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.
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.
- Open SQL Server Management Studio and start an Analysis Services Project.
- Right click on the database that you want to backup and select the Back Up... option.
- 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.
- 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.
- Now, we are going to save the script. Go to File > Save XMLAQuery1.xmla As...
- 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".
- 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.
- Run the following command:
ascmd -S -i c:\backupMD.xmla
- 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.
- 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
View all my tips