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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s