Read Registry For SSAS Backup Directory?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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 :-/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Another option would be powershell. There is a great script by Zakir Durumeric:

    https://zakird.com/code/2011/06/20/backing-up-analysis-services-in-powershell/[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply