I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter.
the code for the stored procedure is below.
the problem:
it works well for not so big databases (up to 4 GB lets say) but when the database is bigger (12 GB) it is giving me the error message below: (I run it from a job scheduled to run every night). I am wondering what could be missing.
error:
Executed as user: mycompany\SQLAgent
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object> <DatabaseID>MYDATABASE BI LIVE</DatabaseID> </Object>
<File>E:\SQLBackups\mydatabase BI LIVE_2014-11-04_235858.abf</File>
<AllowOverwrite>true</AllowOverwrite> </Backup>
[SQLSTATE 01000] (Message 0) OLE DB provider "MSOLAP" for linked server "MYSSASSERVER" returned message "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". [SQLSTATE 01000]
(Message 7412) -- Exception was caught --05 Nov 2014 00:08:58:940
The error number:7215 Severity: 17 State: 1 Procedure: sp_backupSSAS
Line number: 126 Error Message: Could not execute statement on remote server 'myssasserver'.
-- End of Procedure 05 Nov 2014 00:08:58:940 [SQLSTATE 01000] (Message 0). The step succeeded.
THE PROCEDURE SP_BACKUPSSAS:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_backupSSAS] Script Date: 05/11/2014 10:19:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROCEDURE sp_backupSSAS
ALTER PROCEDURE [dbo].[sp_backupSSAS]
@DBS SSAS_DBs_to_BAckup READONLY
/*
=======================================================================
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 [SSASSERVER]
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 ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')
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
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([SSASSERVER], '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 [SSASSERVER]
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
the parameter definition:
USE [master]
GO
CREATE TYPE [dbo].[SSAS_DBs_to_BAckup] AS TABLE(
[DBName] [sysname] NOT NULL,
[LocationName] [varchar](1008) NULL
)
GO
example of how to call the stored procedure:
DECLARE @DBS AS SSAS_DBs_to_BAckup
--INSERT INTO @DBS (DBName, LocationName) VALUES ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')
EXEC sp_backupSSAS @DBS
When I right click the SSAS server in SSMS and look at properties this is what I see:
