SQLServerCentral.com / Discuss content posted by Theo Ekelmans / Article Discussions by Author / Article Discussions / Backup all SSAS databases using TSQL / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:42:16 GMT20RE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHi 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 &lt;ID&gt;CubeName&lt;/ID&gt; , 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:&lt;Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt; &lt;Object&gt; &lt;DatabaseID&gt;CocaCola&lt;/DatabaseID&gt; &lt;/Object&gt; &lt;File&gt;D:\CocaCola.abf&lt;/File&gt;&lt;/Backup&gt;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' ):&lt;Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt; &lt;File&gt;D:\CocaCola.abf&lt;/File&gt; &lt;DatabaseName&gt;Fanta&lt;/DatabaseName&gt;&lt;/Restore&gt;Right-click Databases, left-click Refresh.Mon, 20 Jun 2016 16:15:56 GMTkevin_nikolaiRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspx-- 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=NULLEXEC 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=nullEXEC 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 keptSET @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 aOPEN curCube FETCH NEXT FROM curCube INTO @name WHILE @@FETCH_STATUS = 0 BEGIN Set @XMLA = N' &lt;Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"&gt; &lt;Object&gt; &lt;DatabaseID&gt;' + @name + '&lt;/DatabaseID&gt; &lt;/Object&gt; &lt;File&gt;' + @path + @name + @DT + '.abf&lt;/File&gt; &lt;AllowOverwrite&gt;true&lt;/AllowOverwrite&gt; &lt;/Backup&gt; ';Exec (@XMLA) At [localhost] FETCH NEXT FROM curCube INTO @name END CLOSE curCube DEALLOCATE curCubeMon, 20 Jun 2016 15:11:56 GMTkevin_nikolaiRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHi, First of all thanks for the script. Should I skip the link server execution if my AS and engine is on the same instance? THanksFri, 26 Jun 2015 00:29:03 GMTalgar68RE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxThanks for the script.Wed, 13 May 2015 07:21:07 GMTIwas BornreadyRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxThe easiest solution would be to add DatabaseId to the DBSCHEMA_CATALOGS rowset. I put in a Connect item for it a while ago but it doesn't seem to be a prevalent need. It just seems to me like DatabaseId is a very basic and essential attribute, why leave it out of cube metadata rowsets?[url=https://connect.microsoft.com/SQLServer/Feedback/Details/789357]https://connect.microsoft.com/SQLServer/Feedback/Details/789357[/url]Mon, 20 Apr 2015 09:54:16 GMTkvetterRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspx[quote][b]Theo Ekelmans (4/20/2015)[/b][hr]Hi Lowell,I've never renamed a SSAS DB, so... no i have not had the misfortune to run into this error.I would like to promise here that i will look into this issue on short notice, but alas at the moment we run desperately short of staff, and having to train "the new guys" does'nt help either.But if i do run into this problem or if i get some time to tweak my scripts i *will* look into this, because i was lucky not to have ran into it.grtz,Theo[/quote]Thanks for checking in Theo;I'm more of a SQL guy than Analysis Services, but circumstances beyond my control are making me expand my boundaries quickly.i found it looks like it's a lot easier just to rebuild the darn database by scripting it out, and then processing it again so it populates with data.at the script point, if it didn't do it automatically, i can modify they xlma script and then run it so the name and Id are identical.that seems to be the direction i'll end up going, so my version of your great script would not need to be modified.Mon, 20 Apr 2015 09:42:31 GMTLowellRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxThe only way I've been able to retrieve SSAS metadata to the extent that I can get separate ID and Name properties for SSAS databases is to use the AMO. You can access AMO via vbscript and the like, and also Powershell.Mon, 20 Apr 2015 09:40:52 GMTkvetterRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHi Lowell,I've never renamed a SSAS DB, so... no i have not had the misfortune to run into this error.I would like to promise here that i will look into this issue on short notice, but alas at the moment we run desperately short of staff, and having to train "the new guys" does'nt help either.But if i do run into this problem or if i get some time to tweak my scripts i *will* look into this, because i was lucky not to have ran into it.grtz,TheoMon, 20 Apr 2015 09:29:38 GMTTheo EkelmansRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxTheo thanks for the script!i got it to work on a regular basis on most of my servers that have SSAS, but I've got an exception on one SSAS database: the database was renamed at one point, so the ID is different from the name; the linked server can give us the name of the Database, which we assume is the same as the ID that the xmla script requires.i end up getting this error:[quote]Date 4/16/2015 8:54:02 AMLog Job History (Maint_SSAS Backup All SSAS Databases)Step ID 2Server DOM-SQL-PRODJob Name Maint_SSAS Backup All SSAS DatabasesStep Name Dynamically Build XMLA commands Per DatabaseDuration 00:04:16Sql Severity 17Sql Message ID 7412Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user:MyDomain\dwhc. Could not execute statement on remote server 'SSASLocal'. [SQLSTATE 42000] (Error 7215) OLE DB provider "MSOLAP" for linked server "SSASLocal" returned message "Errors in the metadata manager. Either the database with the ID of 'ClaimTracking' does not exist in the server with the ID of 'DOM-SQL-PROD', or the user does not have permissions to access the object.". [SQLSTATE 01000] (Error 7412). The step failed.[/quote]so the script gets created for ClaimTracking in my case, but the ID the XMLA script should have is technically a different value than the name.[img]http://www.stormrage.com/SQLStuff/SSAS_ID_diff_From_Name.png[/img]have you encountered this since you wrote your article? how did you tackle it?the data from $SYSTEM.DBSCHEMA_CATALOGS doesn't have the id, would you know if a different system catalog has the id tied to the name, so the script could be modified?[b]Edit:[/b] i did my due diligence, and ran [b]exec sp_tables_ex [SSASLocal][/b] on the linked server, and then queries every $SYSTEM table, and visually poked around for the ID, but i had no luck:[code]SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM TABLE_SCHEM.TABLE_NAME') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_TABLES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMAND_OBJECTS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMANDS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CSDL_METADATA') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DB_CONNECTIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DIMENSION_STAT') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_ENUMERATORS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_INSTANCES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_JOBS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_KEYWORDS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LITERALS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LOCKS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MASTER_KEY') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYGRANT') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYUSAGE') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_STAT') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PROPERTIES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_RESOURCE_POOLS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SESSIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_COLUMNS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_DEFINITION_PROVIDERINFO') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_EVENT_CATEGORIES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRANSACTIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_XEVENT_TRACE_DEFINITION') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_FUNCTIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_XML') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODELS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_INPUT_DATASOURCES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_KPIS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEMBERS') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_PROPERTIES') AS aSELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_SETS') AS a[/code]Thu, 16 Apr 2015 07:11:28 GMTLowellRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxInteresting idea.... I'll look into that too :)Fri, 14 Nov 2014 10:18:48 GMTTheo EkelmansRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHi Theo,I am successfully backing up SSAS databases following you idea.It was definitely not straight forward, I had a few issuesas you can see [url=http://dba.stackexchange.com/questions/81928/routine-to-backup-ssas-databases-fails-with-the-xml-for-analysis-request-timed/82485#82485]here[/url] and also [url=http://dba.stackexchange.com/questions/82379/linked-server-from-sql-server-to-ssas-not-working-what-is-missing/82389#82389]here[/url]But in my case:1) I don't have sql server installed on the same machine that I have ssas2) I don't have ssas installed on the same machine that I have sql serverNow the new challenges are:1) how do you do a RESTORE VERIFYONLY in SSAS2) how do you do a DBCC CHECKDB in SSASAnother thing that I needed done is to delete old SSAS backup files.this you can see [url=http://dba.stackexchange.com/questions/83068/run-a-powershell-script-on-a-different-server-from-inside-a-sql-server-job]on this link[/url], I have done it using PowerShell, from a remote computer.It deletes files older than 2 days.All the scripts are there.regardsMarceloThu, 13 Nov 2014 07:21:42 GMTmarcelo miorelliRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxIt's true. But remember that you can disable the service and just run the GUI that also makes the backups. And also exists another options. You can install it in a different machine and makes remotely the backups. All this effort worth it if you want to upload to the cloud, move it through the network, encrypt or something else more complex.You script it's clean and elegant.Fri, 19 Sep 2014 19:40:15 GMTErikVRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHi Erik,I knew of this tool, but this one requires running a seprate service on the SQL server, which is fine if you are allowed do that.If not... you could use my script :)Fri, 12 Sep 2014 08:36:21 GMTTheo EkelmansRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxIf you need something with more options, like compress, upload to the cloud (amazon s3, glacier, dropbox, azure). You can try www.sqlserverbooster.com and it's freeFri, 12 Sep 2014 08:01:42 GMTErikVRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxThis passes the DatabaseName where the DatabaseId should go. This can cause problems for databases that do not have the same value for DatabaseName and DatabaseId.Fri, 31 May 2013 08:59:01 GMTkvetterRE: Backup all SSAS databases using TSQLhttp://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxHello Theo - can I use your script at work? hopefully you dont mind. I need to backup some SSAS databases automatically :)Also what does this do? http://schemas.microsoft.com/analysisservices/2003/engine"I tested in my local computer with no access to the internet and the scrip still worked fine.Thanks' -OswaldoThu, 16 May 2013 11:08:41 GMTOswaldo MoralesBackup all SSAS databases using TSQL http://www.sqlservercentral.com/Forums/Topic1436242-596-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/automatic/97696/">Backup all SSAS databases using TSQL </A>[/B]Wed, 27 Mar 2013 20:07:38 GMTTheo Ekelmans