Issues backing up Analysis Services databases - silent failures

  • I have a PowerShell script that I use to automate the backup of Analysis Services databases - allows for handling new/dropped databases, dynamic filenames, etc.

    However, on some occasions the backup fails but no output is reported stating why. I get a success message when it works but nothing when it fails. I've tried adding a retry loop but this doesn't always solve the problem.

    Is there any way to identify what is going wrong or a better way of achieving what I'm trying to achieve?


  • It depends. Is your powershell script calling a SQL script to handle the backups or are you using a 3rd party backup solution or powershell itself for the backups.

    My approach is different than yours. I like keeping my backups inside SQL. I have my TSQL checking the system tables for online databases to do a backup of and then we have a specific location for those to go based on instance and database name. We copy the script out to all instances and then schedule it as a SQL Agent Job. The script has error handling in it as well as logging built in, so everything is good to go.

    My GUESS is that the powershell script you are using is putting the "error" somewhere you are not expecting. IF it is calling a SQL script, that should report back the errors in the script but is your powershell capturing that?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Can you provide some more details around the error-handling in your PowerShell script? I have found in the past that the meaningful part of the error messages aren't always visible in SQL Agent because of length restrictions.

    I would recommend that you try to write error messages out to a flat file and use the verbose setting, temporarily at least until you've identified the root cause.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply