Post reply

Backup SSAS database

  • October 30, 2014 at 10:15 am

    #300698

    Hi,

    I have installed SSAS on a new server and there is no Database engine is installed on that server. A database is also restored in SSAS. Now client is asking to configure a backup job to take backup of the database in SSAS. Since database engine is not installed, No availability of SQL Agent as well. What are the other ways to take backup of the database?

    Please suggest me. Your immediate response would highly be regarded.

    Thanks

    Bala

  • October 30, 2014 at 12:51 pm

    #1755903

    you can use powershell to backup the SSAS database.

    import-module sqlps

    Backup-ASDatabase -BackupFile "c:\backup\ssasdb.adf" -Server MyServer -Name MyDatabase -AllowCompression

    you could then just schedule the powershell script with windows task scheduler.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • October 30, 2014 at 1:52 pm

    #1755917

    I think you can also schedule a job on another server and use SSIS to send an XMLA Backup command.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 31, 2014 at 3:10 am

    #1755978

    Hi Bob & Keon, Thank you so much for your valuable solutions. I'll try either one of them. Btw, SSIS is installed on that server. That only SSIS and SSAS are insalled, not the Database Engine.

    Thanks

  • October 31, 2014 at 3:41 am

    #1755980

    I have found some interesting stuff related to my requirements in the following link...

    http://www.mssqltips.com/sqlservertip/2654/using-ascmd-to-run-command-line-scripts-for-sql-server-analysis-services/

  • November 4, 2014 at 1:57 pm

    #1756613

    I use a SSIS package (from Vidas Matelis) and a scheduled SQL Agent job with a CMDEXEC step from a different SQL DB Server w/SSIS installed to backup ALL SSAS DB's with compression. I think you could also use Windows Task manager to execute the package as well. (note: I upgraded the downloadable SSIS package in BIDS to run on a 64bit instance of SQL2k8R2)

    http://www.ssas-info.com/VidasMatelisBlog/27_script-to-backup-analysis-services-2005-databases

    How to call the SSIS package from a CMDEXEC step in a SQL Agent Job:

    Working Command from DBServer1 via a 64bit SSIS package via a SQL Agent Job to backup All SSAS DBs on Server2SSAS01:

    dtexec /FILE "F:\SSIS_Packages\SSIS_Backup\DWBackupOLAPDBs.dtsx" /Set \package.variables[BackupLocation].Value;B:\Backups\OLAP\ /Set \package.variables[SSASServerName].Value;Server2SSAS01

    Working Command from DBServer1 via a 64 bit SSIS package via a SQL Agent Job to backup all SSAS DBs on a named instance of SSAS Server2\SSAS01:

    dtexec /FILE "F:\SSIS_Packages\SSIS_Backup\DWBackupOLAPDBs.dtsx" /SET \package.variables[BackupLocation].Value;E:\Backups\ /SET \package.variables[SSASServerName].Value;Server2\SSAS01

Viewing 6 posts - 1 through 5 (of 5 total)

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