SQL Server Performance

Backup method for automation job !

Discussion in 'SQL Server 2005 Analysis Services' started by yukon_dba, Sep 18, 2007.

  1. yukon_dba New Member

    Hello everybody ,
    I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.
    can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ...analysis services 2005 only
    Thanks in advance
    Yukon DBA
  2. ghemant Moderator

  3. satya Moderator

  4. yukon_dba New Member

    we are using sql server 2005 standard edition !
    thanks for the reply
    Yukon DBA
  5. satya Moderator

    You can schedule BACKUP/RESTORE commands to execute on schedule using SQL Server jobs. SQL Server 2005 has a new job step type -- Analysis Services command -- which allows you to execute XMLA statements, including BACKUP and RESTORE.
    If not you can use MSMDARCH command to back up the MSAS database from the command line or from a batch file. The executable file MSMDARCH.exe is found in BIN directory under Analysis Services installation folder, so you will have to change your path to "D: Program FilesMicrosoft Analysis ServicesBin" (if MSAS is installed on D drive) before calling this command.
    To automate a backup, you can create a batch file that contains a command similar to the one above. Then create a data transformation services (DTS) package that calls the batch file through Execute Process -- a task that only needs to know where the batch file is saved. Finally, you would schedule the DTS package to execute as often as needed.
  6. yukon_dba New Member

    i cant use Analysis services command as i have already discussed there is no sql server agent in the instance of analysis server 2005....and as far as i know MSMDARCH is for sql 200 analysis services....where as i am using sql server 2005(standard) ..anywayz i appreciate for ur reply..
    thanks again
    Yukon DBA

Share This Page