1

I have a PROCEDURE sp_backupSSAS - it backups SSAS databases. it works fine.

I had previous problems with it, but they were sorted out here: routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

generally I try to have SSAS on a server of its own - let's say server_SSAS

therefore I use a different server - let's say SERVER_SQL to backup the SSAS databases in the server_SSAS.

for me to run my procedure sp_backupSSAS that lives on server_SQL I first create a table type:

CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
( DBName sysname not null 
  ,LocationName varchar(1008)
);
GO

This table type is used when I call the procedure. It tells me which database(s) to backup and the location where to put them (in the SERVER_SSAS)

From inside a job on SERVER_SQL:

DECLARE @DBS AS SSAS_DBs_to_Backup 

INSERT INTO @DBS (DBName, LocationName) VALUES ('MY Company BI LIVE','H:\SQLBackups\')

EXEC sp_backupSSAS @DBS

This creates a backup on my server_SSAS which is called SQLBILON1:

enter image description here

That is all working fine, however, I would like to pass the name of the server_SSAS which in this case was SQLBILON1 as a parameter, and NOT have it hard-coded inside the procedure.

how can I achieve this?

the full code of the procedure is here:

--SELECT @@SERVERNAME
PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''





USE MASTER 
GO


--==================================================================================
--create a table type to contain the list of SSAS databases we want to backup
--==================================================================================


CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
( DBName sysname not null 
  ,LocationName varchar(1008)
);
GO

BEGIN TRY
DROP PROCEDURE sp_backupSSAS
END TRY
BEGIN CATCH
END CATCH

GO

CREATE PROCEDURE sp_backupSSAS
   @DBS  SSAS_DBs_to_BAckup READONLY,
   @Server VARCHAR(108)


/*
=======================================================================
Script  :   PROCEDURE sp_backupSSAS
Author  :   Marcelo Miorelli
Date    :   03-NOV-2014
Desc    :   
            Backup the SSAS databases that are on table @DBS
            needs the LINKED server to SSAS - in this case [SQLBILON1]
            On the first version I left that static.

Credits:  the credits for the Original idea  go to Theo Ekelmans
          http://www.sqlservercentral.com/scripts/automatic/97696/



Usage   :           

DECLARE @DBS AS SSAS_DBs_to_BAckup 
--INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI LIVE','H:\SQLBackups\SQLBILON1\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SQLBILON1\')

EXEC sp_backupSSAS @DBS


=======================================================================
History
Date        Action      User            Desc
-----------------------------------------------------------------------
<Add Date>  Created     <add your name> <add description of change>

=======================================================================
*/

--======================================
-- describe primary blocks of processing
--======================================

------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------

-- describe individual actions within a command set

WITH ENCRYPTION
AS 


SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


DECLARE @name VARCHAR(50) -- Cube name  
DECLARE @path VARCHAR(256) -- 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


-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
Set @DT = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');

DECLARE @RADHE TABLE ( i int not null identity(1,1) PRIMARY KEY CLUSTERED,
                       CATALOG_NAME  SYSNAME NOT NULL)

DECLARE @I INT
DECLARE @Z INT

DECLARE  @log NVARCHAR(MAX)
        ,@vCrlf CHAR(2);

SELECT  @log = ''
       ,@vCrlf = CHAR(13)+CHAR(10);

INSERT INTO @RADHE (CATALOG_NAME) 
SELECT CATALOG_NAME 
FROM openquery([SQLBILON1], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as a


SELECT @Z = @@ROWCOUNT
SELECT @I = 1


WHILE @I <= @Z BEGIN

  SELECT @NAME = CATALOG_NAME 
    FROM @RADHE WHERE I = @I

  --====================================================================
  -- check whether the database is contained in @DBS 
  -- get the path from @DBS where you want to backup the database
  -- if yes then backup the database, if not then skip
  --====================================================================

  SELECT @PATH = NULL

  SELECT @path = LocationName FROM @DBS WHERE DBName = @NAME 

    IF (@PATH IS NOT NULL) BEGIN 

             ---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>' + @path + @name + @DT + '.abf</File>
                <AllowOverwrite>true</AllowOverwrite>
             </Backup>
             ';


            BEGIN TRY

                --print CAST (@XMLA AS NTEXT)

                -- Execute the string across the linked server (SSAS)
                Exec (@XMLA) At [SQLBILON1]

            END TRY
            BEGIN CATCH

                    SELECT
                            ERROR_NUMBER() AS ErrorNumber,
                            ERROR_SEVERITY() AS ErrorSeverity,
                            ERROR_STATE() AS ErrorState,
                            ERROR_PROCEDURE() AS ErrorProcedure,
                            ERROR_LINE() AS ErrorLine,
                            ERROR_MESSAGE() AS ErrorMessage


                            SELECT @log = @log + '-- Exception was caught --' +  CONVERT(VARCHAR(24), GETDATE(), 113)  + @vCrlf  +
                                                 'The error number:' + coalesce(cast ( ERROR_NUMBER()  as varchar(max)), 'No Info') + @vCrlf  

                            SELECT @log = @log + 'Severity: '        + coalesce(cast ( ERROR_SEVERITY()  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'State: '           + coalesce(cast ( ERROR_STATE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Procedure: '       + coalesce(cast ( coalesce(ERROR_PROCEDURE(),'No Info')  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'Line number: '     + coalesce(cast ( ERROR_LINE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Error Message: '   + cast ( coalesce(ERROR_MESSAGE(),'No Info')  as varchar(max)) + @vCrlf  

                            SELECT @log = @log + ' -- End of Procedure ' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf  

                            PRINT CAST(@LOG AS NTEXT) + @vCrlf  

                            WHILE @@TRANCOUNT > 0 
                                    ROLLBACK

            END CATCH




    END --IF

    SELECT @I += 1

END   

GO

exec sys.sp_MS_marksystemobject 'sp_backupSSAS'

GO
0

this procedure runs XMLA statements in a SSAS server from a SQL Server.

first we need to create the linked server to the SSAS. this you can see on the link below:

How can I reduce OLEDB wait type in a SSAS linked server connection?

After that I have done some changes on the procedure I have posted on the question.

I will share the script here, so that it is documented.

--SELECT @@SERVERNAME
PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''


USE MASTER 
GO

BEGIN TRY
exec sp_save @TableName ='sp_backupSSAS',@permissions='Y'
END TRY
BEGIN CATCH
print error_message()
END CATCH
GO

--==================================================================================
--create a table type to contain the list of SSAS databases we want to backup
--==================================================================================

BEGIN TRY

IF NOT EXISTS (select * from sys.types where name = 'SSAS_DBs_to_Backup') 
        CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
        ( DBName sysname not null 
          ,LocationName varchar(1008)
        );

END TRY
BEGIN CATCH
print error_message()
END CATCH
GO


BEGIN TRY
DROP PROCEDURE sp_backupSSAS
END TRY
BEGIN CATCH
END CATCH

GO

CREATE PROCEDURE sp_backupSSAS
   @DBS  SSAS_DBs_to_BAckup READONLY,
   @SERVER NVARCHAR(128)


/*
=======================================================================
Script  :   PROCEDURE sp_backupSSAS
Author  :   Marcelo Miorelli
Date    :   03-NOV-2014
Desc    :   
            Backup the SSAS databases that are on table @DBS
            needs the LINKED server to SSAS - in this case [SQLBILON1]
            On the first version I left that static.

Credits:  the credits for the Original idea  go to Theo Ekelmans
          http://www.sqlservercentral.com/scripts/automatic/97696/

Parameters: the @Server must be an already created linked server to the SSAS instance

            regarding the @DBS

            DBname is the database name
            LocationName is the path how it is seen inside the @Server.

            for instance H:\SSASBackup\ is a folder inside the server SASBIDEV01  

Usage   :           

DECLARE @DBS AS SSAS_DBs_to_BAckup 
INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI SSAS AutoDeploy 9_01','H:\SSASBackup\')

EXEC sp_backupSSAS @DBS, 'SASBIDEV01'


=======================================================================
History
Date           Action             User                 Desc
-----------------------------------------------------------------------
15-sep-2015    added feature      marcelo miorelli     added the @server as a parameter 
                                                       @server is a linked server to a SSAS server.

                                                       changed the dynamic sql to run XMLA commands on SSAS

=======================================================================
*/

--======================================
-- describe primary blocks of processing
--======================================

------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------

-- describe individual actions within a command set

WITH ENCRYPTION
AS 


SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @sql NVARCHAR(MAX)
DECLARE @name VARCHAR(50) -- Cube name  
DECLARE @path VARCHAR(256) -- 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


-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
Set @DT = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');

DECLARE @RADHE TABLE ( i int not null identity(1,1) PRIMARY KEY CLUSTERED,
                       CATALOG_NAME  SYSNAME NOT NULL)

DECLARE @I INT
DECLARE @Z INT

DECLARE  @log NVARCHAR(MAX)
        ,@vCrlf CHAR(2);

SELECT  @log = ''
       ,@vCrlf = CHAR(13)+CHAR(10);

---------------------------------------------------------------------------------------------

SELECT @SERVER = COALESCE(UPPER(LTRIM(RTRIM(@SERVER))),@@SERVERNAME)


IF NOT EXISTS(
                SELECT * 
                  FROM SYS.SERVERS
                  WHERE NAME = @SERVER
                    AND provider = 'MSOLAP'
                    AND is_data_access_enabled = 1
) BEGIN

           SELECT @SQL = 'The server called %s cound not be found, or does not have data access enabled.' + @vCrlf + @vCrlf +
                         'sp_backupSSAS usage:' + @vCrlf +
                         'DECLARE @DBS AS SSAS_DBs_to_BAckup ' + @vCrlf +
                         'INSERT INTO @DBS (DBName, LocationName) VALUES (''Pyramid Demo 2013'',''\\SQLREPLON1\SQLBackups\SQLBILON1\'') ' + @vCrlf +
                         'EXEC sp_backupSSAS @DBS, ''SASBI01''' + @vCrlf 

           RAISERROR(@SQL ,16,1,@SERVER)
           RETURN (-1)


END 
---------------------------------------------------------------------------------------------

                SELECT @SQL = 'SELECT CATALOG_NAME 
                              FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS' + '''' + ')'

                INSERT INTO @Radhe
                exec ( @sql)


SELECT @Z = @@ROWCOUNT
SELECT @I = 1

WHILE @I <= @Z BEGIN

  SELECT @NAME = CATALOG_NAME 
    FROM @RADHE WHERE I = @I

  --====================================================================
  -- check whether the database is contained in @DBS 
  -- get the path from @DBS where you want to backup the database
  -- if yes then backup the database, if not then skip
  --====================================================================

  SELECT @PATH = NULL

  SELECT @path = LocationName FROM @DBS WHERE DBName = @NAME 

    IF (@PATH IS NOT NULL) BEGIN 

             ---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>' + @path + @name + @DT + '.abf</File>
                <AllowOverwrite>true</AllowOverwrite>
             </Backup>
             ';


            BEGIN TRY

                print CAST (@XMLA AS NTEXT)
                --Execute the string across the linked server (SSAS)


                SELECT @SQL = 'SELECT 
                                [n],
                                [Login_Name],
                                [Login_From],
                                [Account_Type]              
                              FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + @XMLA + '''' + ')'


                SELECT @SQL = 'exec (' + '''' + @XMLA + '''' + ')' + ' AT ' + quotename(@SERVER )

                print CAST (@sql AS NTEXT)

                exec (@SQL)



                --Exec (@XMLA) At [SQLBILON1]

                print 'backup is done - check the folder ' + @path

            END TRY
            BEGIN CATCH

                    SELECT
                            ERROR_NUMBER() AS ErrorNumber,
                            ERROR_SEVERITY() AS ErrorSeverity,
                            ERROR_STATE() AS ErrorState,
                            ERROR_PROCEDURE() AS ErrorProcedure,
                            ERROR_LINE() AS ErrorLine,
                            ERROR_MESSAGE() AS ErrorMessage


                            SELECT @log = @log + '-- Exception was caught --' +  CONVERT(VARCHAR(24), GETDATE(), 113)  + @vCrlf  +
                                                 'The error number:' + coalesce(cast ( ERROR_NUMBER()  as varchar(max)), 'No Info') + @vCrlf  

                            SELECT @log = @log + 'Severity: '        + coalesce(cast ( ERROR_SEVERITY()  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'State: '           + coalesce(cast ( ERROR_STATE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Procedure: '       + coalesce(cast ( coalesce(ERROR_PROCEDURE(),'No Info')  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'Line number: '     + coalesce(cast ( ERROR_LINE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Error Message: '   + cast ( coalesce(ERROR_MESSAGE(),'No Info')  as varchar(max)) + @vCrlf  

                            SELECT @log = @log + ' -- End of Procedure ' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf  

                            PRINT CAST(@LOG AS NTEXT) 

                            WHILE @@TRANCOUNT > 0 
                                    ROLLBACK

            END CATCH




    END --IF

    SELECT @I += 1

END   

GO

exec sys.sp_MS_marksystemobject 'sp_backupSSAS'
GO
print 'The Procedure sp_backupSSAS has been created.'
GO

Regarding the usage:

DECLARE @DBS AS SSAS_DBs_to_BAckup 
INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI SSAS AutoDeploy 9_01','H:\SSASBackup\')

EXEC sp_backupSSAS @DBS, 'SASBIDEV01'

that means that this procedure will backup the ssas database called Boden BI SSAS AutoDeploy 9_01 on server SASBIDEV01 and the backup will be saved on server SASBIDEV01 folder *H:\SSASBackup*

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.