Perform Analysis Services database backups using Powershell

I was recently looking into performing SQL Server Analysis Services database backups using Powershell within an automated task, with the following requirements: 

  • Perform a backup of all databases for running instances of Analysis Services.
  • If the target location does not exist, create the directory.

Firstly we need to load the Microsoft SQL Server Analysis Services binaries in order to connect to the instance and invoke actions. 

Capture1

I will also retrieve the computername and store this in a variable to use later in the server connection properties. 

Capture2

Using the Get-Service cmdlet, I will return all running instances  by filtering the output to include where the service name is like *MSOLAP$* and the status is ‘Running’.

Capture3

Once, I have returned the running instances of Analysis Services I will loop through each one to firstly obtain the instance name by manipulating the service name returned in my collection and then connecting to the instance using the binaries previously loaded into the powershell session. 

Capture4

Now, I will be required to loop through each database within the instance and to check if the target location exists and if not create the folder. 

Capture5

Finally, we will create a backup of the database to which I will append the date string ddMMyyyy_HHmmss to the the filename. 

Capture6

The above can be automated by creating a task in Task Scheduler and configuring a trigger to perform the action of invoking the powershell script. 

The powershell script can be downloaded from: 

 https://app.box.com/s/x000axp5qu37y3uzs1ue

Advertisements

Change the port number of SSAS Instance

If you have a named instance of Analysis Services this will listen on a dynamic TCP port which is assigned when the service starts.

Only a default instance will listen on TCP port 2383.

In order to change this to a static port follow the below steps;

1) Connect to the Analysis Services instance using Microsoft SQL Management Studio.

2) Select ‘Properties’

3) Select ‘General’

4) Browse to ‘Port’ and change the value to the specified TCP port. (By default this value should be ‘0’)

5) Select ‘OK’

6) Restart the Analysis Services instance.

You can confirm the Analysis Services instance is now listening on the above port by running the following command:

netstat /aob

Search for the TCP port you have specified in Step 4 and confirm the executable ‘msmdsrv.exe’ is listening on this port.