DECLARE

@day char(2) = UPPER(SUBSTRING (datename(DW,getdate()-1),0,3))

,@folder nvarchar(2000) = N'C:\Temp\'

,@as nvarchar(128) = N'AnalysisServices'

DECLARE

@server SYSNAME = @@SERVERNAME

,@linkedServer SYSNAME = (SELECT name FROM sys.servers WHERE provider = 'MSOLAP' AND data_source = 'localhost')

/****** create temp table, only if you need to backup specific AS DBs ******/

DECLARE

@db nvarchar (50) = 'tempdb'

,@schema nvarchar(10) = '.dbo.'

,@temptable nvarchar(max) = N'TestAS'

DECLARE

@drop nvarchar(max) = N'DROP TABLE '+ @db + @schema + @temptable

,@selectinto nvarchar(max) = N'SELECT [OBJECT_ID] INTO '+ @db + @schema + @temptable + ' FROM OPENQUERY(['+ @linkedServer +'],N''SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''''' + @server + '.Databases'''''')'

DECLARE

@table nvarchar(max) = N'IF EXISTS (SELECT 1 FROM '+ @db +'.[sys].[objects] WHERE name = '''+ @temptable +''')

BEGIN

'+ @drop +'

'+ @selectinto +'

END

ELSE

BEGIN

'+ @selectinto +'

END'

EXEC sp_executesql @table

/****** check target folder ******/

DECLARE

@sqlEXIST nvarchar(2000) = N'EXEC master..xp_fileexist '''+ @folder + @as +'\' + @day + '\'''

,@sqlCREATE nvarchar(2000) = N'EXEC master.dbo.xp_create_subdir '''+ @folder + @as +'\'+ @day + '\'''

DECLARE

@sql nvarchar(max) = N'IF OBJECT_ID(''tempdb..#temp_db_test'') IS NOT NULL DROP TABLE #temp_db_test;

IF OBJECT_ID(''tempdb..#temp_db_test'') IS NULL CREATE TABLE #temp_db_test (FileExists int, IsDirectory int, ParentDirExists int);

INSERT INTO #temp_db_test '+ @sqlEXIST +';

IF NOT EXISTS(SELECT IsDirectory FROM #temp_db_test WHERE IsDirectory=1) '+ @sqlCREATE +';'

EXEC sp_executesql @sql

DECLARE

@db_name VARCHAR(50)

,@xmla nvarchar(max)

--,@cursor nvarchar(max) = N'DECLARE cur CURSOR FOR SELECT [OBJECT_ID] FROM OPENQUERY(['+ @linkedServer +'],N''SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''''' + @server + '.Databases'''''')' -- for all AS DBs

,@cursor nvarchar(max) = N'DECLARE cur CURSOR FOR SELECT [OBJECT_ID] FROM '+ @db + @schema + @temptable +' WHERE [OBJECT_ID] NOT LIKE ''%TEST%''' --only if you need to backup specific AS DBs

EXEC sp_executesql @cursor

OPEN cur

FETCH NEXT FROM cur INTO @db_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @xmla = N'''<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>'+ @db_name +'</DatabaseID>

</Object>

<File>'+ @folder + @as +'\'+ @day +'\'+ @db_name +'.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>'''

DECLARE

@exec nvarchar(4000) = N'EXEC ('+ @xmla +') AT '+@linkedServer

EXEC sp_executesql @exec

FETCH NEXT FROM cur INTO @db_name

END

CLOSE cur

DEALLOCATE cur

EXEC sp_executesql @drop --optional