Backup all SSAS databases using TSQL

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  June 20, 2016 at 3:11 pm

  #1885925

  -- Below is based on Theo's script (just modified to run on local server (not remote server)

  USE [master]

  -- Setup a linked-server first (only need to do once off)

  -- I use localhost as servername (because I am doing backups locally - not to remote server)

  EXEC master.dbo.sp_addlinkedserver @server = N'localhost', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'localhost',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'collation compatible', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'data access', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'dist', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'pub', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'rpc', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'rpc out', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'sub', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'connect timeout', @optvalue=N'0'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'collation name', @optvalue=null

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'lazy schema validation', @optvalue=N'false'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'query timeout', @optvalue=N'0'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'use remote collation', @optvalue=N'true'

  EXEC master.dbo.sp_serveroption @server=N'localhost', @optname=N'remote proc transaction promotion', @optvalue=N'true'

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

  -- Add the below part to SQL Server Agent Job

  -- I use localhost as servername (because I am doing backups locally - not to remote server)

  DECLARE @name VARCHAR(50)

  DECLARE @path VARCHAR(256)

  DECLARE @fileName VARCHAR(256)

  DECLARE @dt VARCHAR(20)

  Declare @XMLA nvarchar(4000)

  -- D:\SQL_backup is the folder where SSAS backups will be kept

  SET @path = 'D:\SQL_backup\' + '\'

  Set @dt = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');

  DECLARE curCube CURSOR FOR

  SELECT CATALOG_NAME

  FROM openquery([localhost], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as a

  OPEN curCube

  FETCH NEXT FROM curCube INTO @name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  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>

  ';

  Exec (@XMLA) At [localhost]

  FETCH NEXT FROM curCube INTO @name

  END

  CLOSE curCube

  DEALLOCATE curCube

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  June 20, 2016 at 4:15 pm

  #1885929

  Hi Lowell, I agree, the following will give Catalog Name:

  SELECT * FROM openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS')

  Method 1:

  If you connect to Analysis Services via SSMS, then right-click on the Analysis Services database, then left-click on Properties, you will get the Name and ID (ID is same as Name). You will notice you can change the Name but not the ID. Make a backup of the Analysis Services database first. Open Visual Studio project, right-click the Cube and left-click View Code. The second line will list <ID>CubeName</ID> , here you can change the ID , then click Save All, then Build Project, then right-click the Cube and left-click Process.

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

  Method 2: (recommended)

  Backup your SSAS database via SSMS ( Analysis Services ) using below query:

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

  <DatabaseID>CocaCola</DatabaseID>

  </Object>

  <File>D:\CocaCola.abf</File>

  </Backup>

  Right-click your database and click Delete.

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

  Restore the SSAS database you deleted via SSMS ( Analysis Services ) using below query , ( to change name Replace 'CocaCola' with 'Fanta' ):

  <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <File>D:\CocaCola.abf</File>

  <DatabaseName>Fanta</DatabaseName>

  </Restore>

  Right-click Databases, left-click Refresh.

• sureshkurapati

  Newbie

  Points: 3

  More actions

  February 9, 2024 at 6:51 pm

  #4376088

  Hello Theo

  First of all , a BIG thank you for the script .This has definitely helped me to migrate SSAS databases to a new server with backup/restore.

  Now , I am setting a SQL agent job that runs daily to backup databases on the new server on daily basis . Backups works perfectly fine when I execute it from Query Analyzer window. However , when I execute the same script from SQL agent , nothing backups . Job does not throw any error. It finishes sucessfully , but , I do not see a backup . I am running the job of type 'Transact-SQL script (TSQL)' . When I tried running as a 'SQL Services Analysis Command' type, SQL throws this error 'The @flags parameter is not valid for a job step of type 'ANALYSISCOMMAND'. (Framework Microsoft SqlClient Data Provider)'.