Backup method for automation job ! | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Backup method for automation job !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
You can create a batch file for backup your database and schedule it using windows schedular. Here is the referrence http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=487021&SiteID=1 and http://www.sqldbatips.com/showarticle.asp?ID=27
What is the edition of SQL you are using?
http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx & http://support.microsoft.com/kb/312399 for SQL 2k in case if you areusing.
we are using sql server 2005 standard edition !
thanks for the reply
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.
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..