Backup all Analysis Services databases to drive

create  procedure db_ArchiveAnalysisServicesDBs
	@ASServer	varchar(25),
	@ASPath		varchar(100),
	@BkpPath	varchar(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 @DatabaseName	varchar(45),
	@Command	varchar(550),
	@SQLCommand	Nvarchar(2500)



-- Declare and open the database name cursor.
	--	select distinct(rtrim(DstName))
	--		from msdb..RTblRelships
	--		where	OrgTypeID = 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