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:
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
Best Answer
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.
Regarding the usage: