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.
I will also retrieve the computername and store this in a variable to use later in the server connection properties.
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’.
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.
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.
Finally, we will create a backup of the database to which I will append the date string ddMMyyyy_HHmmss to the the filename.
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:
Deans Blog 