Backup all Analysis Services databases to drive

create  procedure db_ArchiveAnalysisServicesDBs
@ASServervarchar(25),
@ASPathvarchar(100),
@BkpPathvarchar(100),
@RepositoryDB   varchar(55)

as

/************************************************************************
 * This procedure will archive all of the Analysis
 * Services databases to a drive on the server.
 *
 * Two days worth of archives will be saved.
 ************************************************************************/
-- Declare local variables.
declare @DatabaseNamevarchar(45),
@Commandvarchar(550),
@SQLCommandNvarchar(2500)



-- Declare and open the database name cursor.
--select distinct(rtrim(DstName))
--from msdb..RTblRelships
--whereOrgTypeID = 0x1E00000035370000 and
--Z_OrgVE_Z = 2147483647 and
--DstName not like 'FoodMart%'
--group by DstName
select @SQLCommand = ('declare Database_cur INSENSITIVE cursor for ' +
'select rtrim(ObjectName) from ' + @RepositoryDB + '..OlapObjects ' +
'where ClassType = 2 and ObjectName not like ''FoodMart%'' ' +
'order by ObjectName for read only')
--print @SQLCommand
EXEC sp_executesql @SQLCommand

open Database_cur
fetch Database_cur into @DatabaseName

/*
 * Loop.
 */while ( @@fetch_status = 0 )
BEGIN

select '*** Processing database=' + @DatabaseName


-- Delete the 2 day old archives.
set @Command = ('del "' + @BkpPath + @DatabaseName + '2.cab"')
print @Command
EXEC master..xp_cmdshell @Command
 
set @Command = ('del "' + @BkpPath + @DatabaseName + '2.log"')
print @Command
EXEC master..xp_cmdshell @Command


-- Rename the previous day's archives to another name.
set @Command = ('rename "' + @BkpPath + @DatabaseName + '.cab" "' +
@DatabaseName + '2.cab" ')
print @Command
EXEC master..xp_cmdshell @Command
 
set @Command = ('rename "' + @BkpPath + @DatabaseName + '.log" "' +
@DatabaseName + '2.log" ')
print @Command
EXEC master..xp_cmdshell @Command


-- Archive the database.
set @Command = ('msmdarch /A ' + rtrim(@ASServer) +
' "' + @ASPath + '" "' + @DatabaseName +
'" "' + @BkpPath + @DatabaseName + '.cab"' + 
' "' + @BkpPath + @DatabaseName + '.log"')
print @Command
EXEC master..xp_cmdshell @Command


fetch Database_cur into @DatabaseName

END
/*
 * End of loop.
 */

-- Close and deallocate the cursor.
close Database_cur
deallocate Database_cur


RETURN