April 15, 2015 at 10:12 am
How can i read the registry for the default backup directory for my local SSAS instance?
in a SQL Server, if i'm building dynamic queries, i can read the registry of the instance and get it's default backup directory:
now i need to do the same for the SSAS instance on the same machine.
i'm building a cursor that backs up my SSAS databases, but i'd like to put them in the "right" folder, instead of hard coding it.
/*--Results:
E:\SQLBackups\
*/
DECLARE @BackupDirectory NVARCHAR(MAX)
--@BackupDirectory
DECLARE @RegResults TABLE([VALUE] VARCHAR(512),[DATA] VARCHAR(1024));
INSERT INTO @RegResults([VALUE],[DATA])
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory';
SELECT @BackupDirectory = ISNULL([DATA],'') FROM @RegResults;
IF LEFT(REVERSE(@BackupDirectory),1) <> N'\'
SELECT @BackupDirectory = @BackupDirectory + N'\';
SELECT @BackupDirectory AS BackupDirectory
Lowell
May 1, 2015 at 8:20 am
From what I was able to find, the default backup folder of an SSAS instance is neither stored in the registry nor is it accessible through the SSAS DMVs.
It seems like the only place it is stored is in the SSAS configuration file (msmdsrv.ini). There should be a msmdsrv.bak (backup version) in the same folder (<SSAS Installation Folder>/<Instance Name>/OLAP/Config) which you will have to interrogate for the value (<BackupDir>) XML element.
Would have been nice if these were accessible through the DMVs, but no such luck :-/
May 1, 2015 at 10:19 am
Thanks for the heads up, Martin;
since i was doing this in TSQL, i went ahead and just used the SQL server backup directory for my backups, instead of the default SSAS directory.
it's just a job on SQL Server that hits a linked server pointing to the local SSAS instance, so it's working as expected.
i adapted it from this Theo Ekelmans' article here
http://www.sqlservercentral.com/scripts/automatic/97696/
--#################################################################################################
-- http://www.sqlservercentral.com/scripts/automatic/97696/
-- Name : Backup all SSAS databases
-- Description : Enumerates and backups all SSAS databases using MSOLAP linked server
-- Used By : Backup all job
-- Author : Theo Ekelmans
-- Version/Date : 1.0, 2012-03-13
--#################################################################################################
DECLARE @name VARCHAR(50) -- Cube name
DECLARE @BackupDirectory NVARCHAR(MAX) -- Backup path
DECLARE @fileName VARCHAR(256) -- Backup filename
DECLARE @dt VARCHAR(20) -- Used for optional file name timestamp
DECLARE @XMLA NVARCHAR(4000) -- The SSAS command in XML format
--@BackupDirectory
DECLARE @RegResults TABLE([VALUE] VARCHAR(512),[DATA] VARCHAR(1024));
INSERT INTO @RegResults([VALUE],[DATA])
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory';
SELECT @BackupDirectory = ISNULL([DATA],'') FROM @RegResults;
IF LEFT(REVERSE(@BackupDirectory),1) <> N'\'
SELECT @BackupDirectory = @BackupDirectory + N'\';
--SELECT @BackupDirectory AS BackupDirectory
/*
-- Setup a linked server first:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SSASLocal', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSASLocal',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSASLocal', @optname=N'remote proc transaction promotion', @optvalue=N'true'
*/
-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
SET @dt = '_' + Replace(Replace(CONVERT(NVARCHAR, Getdate(), 120), ':', ''), ' ', '_');
DECLARE curCube CURSOR FOR
SELECT CATALOG_NAME
FROM Openquery([SSASLocal], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') AS a
OPEN curCube
FETCH NEXT FROM curCube INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create the XMLA string (overwrites the same files again and again)
--Set @XMLA = N'
--<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
-- <Object>
-- <DatabaseID>' + @name + '</DatabaseID>
-- </Object>
-- <File>' + @BackupDirectory + @name + '.abf</File>
-- <AllowOverwrite>true</AllowOverwrite>
--</Backup>
--';
-- Create the XMLA string (add a DT stamp to the filename)
SET @XMLA = N'
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>' + @name + '</DatabaseID>
</Object>
<File>' + @BackupDirectory
+ @name + @dt + '.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
';
-- Execute the string across the linked server (SSAS)
EXEC (@XMLA) At [SSASLocal]
FETCH NEXT FROM curCube INTO @name
END
CLOSE curCube
DEALLOCATE curCube
Lowell
May 1, 2015 at 2:16 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply