{"id":391,"date":"2015-09-03T05:53:42","date_gmt":"2015-09-03T05:53:42","guid":{"rendered":"http:\/\/www.exploreax.com\/blog\/?p=391"},"modified":"2015-09-11T06:36:52","modified_gmt":"2015-09-11T06:36:52","slug":"ax-db-restore-scripts-2-ssas-servers","status":"publish","type":"post","link":"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/03\/ax-db-restore-scripts-2-ssas-servers\/","title":{"rendered":"AX DB Restore Scripts #2 \u2013 SSAS Servers"},"content":{"rendered":"<p>Recently\u00a0I started a<a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/01\/ax-database-restore-scripts-list\/\"> series of posts<\/a> on DB restore scripts for Dynamics AX by providing a <a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/01\/ax-database-restore-scripts-list\/\">list <\/a>of data entities in your AX database\u00a0that may need to\u00a0be updated when restoring from one environment to another, in particular a production DB to a development or QA environment.<\/p>\n<p>Today I will be\u00a0drilling into how to reset the\u00a0<strong>SSAS (SQL Server analysis\u00a0services)<\/strong> configurations via DB scripts.<\/p>\n<p>In the Dynamics AX 2012 client these configurations are located\u00a0under System Administration -&gt; Setup -&gt; Business Intelligence -&gt; Analysis\u00a0Services -&gt; Analysis\u00a0Servers.<\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/Analysis.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-393\" src=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/Analysis-300x175.png\" alt=\"Analysis\" width=\"300\" height=\"175\" srcset=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/Analysis-300x175.png 300w, http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/Analysis.png 691w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following:<\/p>\n<p>1. Remove all but one of the SSAS configurations<br \/>\n2. Update the server details of the remaining configuration<\/p>\n<p>The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.<\/p>\n<pre class=\"lang:mysql decode:true\">Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\\AX'\r\nDeclare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration\r\n\r\ndelete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)\r\nupdate BIAnalysisServer set \r\n    SERVERNAME=@SSASSERVERNAME, \r\n    DESCRIPTION=@SSASDESCRIPTION,\r\n\tISDEFAULT = 1\r\nWHERE SERVERNAME &lt;&gt; @SSASSERVERNAME -- Optional where clause if you want to see the \"affected rows<\/pre>\n<p>Note: These changes assume everything is setup correctly on the SSAS server itself.<\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/2015-09-03_0751.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-394\" src=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/09\/2015-09-03_0751.png\" alt=\"2015-09-03_0751\" width=\"262\" height=\"212\" \/><\/a><\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/04\/ax-db-restore-scripts-3-configure-ax-servers-and-batch-servers\/\">View Next &#8211;\u00a0AX DB Restore Scripts #3 \u2013 Configure AX servers and batch servers<\/a><\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/02\/ax-db-restore-scripts-1-ssrs-servers\/\">View Previous &#8211; AX DB Restore Scripts #1 \u2013 SSRS Servers<\/a><\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/01\/ax-database-restore-scripts-list\/\">Back to List<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently\u00a0I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database\u00a0that may need to\u00a0be updated when restoring from one environment to another, in particular a production DB to a development or QA environment. Today I will be\u00a0drilling into how to reset the\u00a0SSAS&hellip;<a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/09\/03\/ax-db-restore-scripts-2-ssas-servers\/\">Read more <span class=\"screen-reader-text\">AX DB Restore Scripts #2 \u2013 SSAS Servers<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/391"}],"collection":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/comments?post=391"}],"version-history":[{"count":6,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/391\/revisions"}],"predecessor-version":[{"id":438,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/391\/revisions\/438"}],"wp:attachment":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/media?parent=391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/categories?post=391"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/tags?post=391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}