June 20, 2016 at 3:11 pm
-- 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
June 20, 2016 at 4:15 pm
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.
February 9, 2024 at 6:51 pm
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)'.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply