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.
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.
- 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
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
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018