自动备份SSAS数据库

Use XMLA to backup SSAS databases. Below is an simple example: 

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
  <Object> 
    <DatabaseID>Microsoft_Analytics</DatabaseID> 
  </Object> 
  <File>\\Prodsqlbackups\backups\SSAS\Microsoft_Analytics.abf</File> 
  <AllowOverwrite>true</AllowOverwrite> 
</Backup> 

The whole XMLA element described in below page: 
http://technet.microsoft.com/en-us/library/ms186622(v=sql.105).aspx 

We can schedule below sql inside sql job to execute backups. In this way, we can build xmla script dynamically so we can change backup files name dynamically. 
In this way, we could hold multiple backup files for one SSAS database in one folder. 

exec ('xmla script') at SSASLocal -- SSASLocal is linked server to SSAS. 

The only downside for this solution is: 

Every time when there's a new SSAS database needs to be backed up, we need to create sql job manually. Seems there's no automatic way to add new SSAS database into backup plan like Litespeed does. 

下面是创建到SSAS数据库的linked server 脚本：

USE [master]GO/****** Object:  LinkedServer [SSASLocal]    Script Date: 5/3/2014 9:28:28 PM ******/EXEC master.dbo.sp_addlinkedserver @server = N'SSASLocal', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSASLocal',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation compatible', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'data access', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'dist', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'pub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc out', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'sub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'connect timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation name', @optvalue=nullGOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'lazy schema validation', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'query timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'use remote collation', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'remote proc transaction promotion', @optvalue=N'true'