Skip to content

Automated backups using PowerShell and the SQL Server Agent

March 5, 2012

One of the least known features of SQL Server 2012 is the addition of several PowerShell cmdlets for managing Analysis Services servers. See my previous blog post on using PowerShell cmdlets for managing tabular models for more information. When I was fiddling with the SQL Server Agent recently, I noticed that the Agent can execute PowerShell scripts automatically. Just for fun, let’s put these two things together and create a basic SQL Agent job that automatically backs up an Analysis Services database using PowerShell.

First – the prerequisites. Make sure the target server satisfies the lengthy list of prerequisites for using AMO for PowerShell. Next, ensure that the user running the SQL Server Agent service has sufficient privileges to back up the desired Analysis Services database. If you need to, run services.msc, change the user running the SQL Agent service, and restart the service.

Now you can go ahead and create the job. To take a backup of a database on the localhost\tabular instance, paste the following script into a PowerShell step in a SQL Server Agent job:

cd sqlas\localhost\tabular
backup-asdatabase "rosling.abf" hans_rosling_project -ApplyCompression

This script works, but it has a problem. The script always succeeds! Try it yourself and see. Because this script does not allow overwriting of the backup file, the backup-asdatabase command will fail on the second try. However, the SQL Agent job succeeds. Only when you drill down into the history of each individual job step do you discover the reason for failure.

We definitely want this job to fail if backup fails so that notification can be triggered. Here is how to do it. The SQL Agent will fail job steps if an unhandled exception is thrown at any time. Since backup-asdatabase does not throw unhandled exceptions, we need to throw one ourselves if the cmdlet returns an error. We can capture the error output of the backup-asdatabase cmdlet by supplying a variable to the built-in –ErrorVariable parameter. If this variable is not empty, we can throw an exception.

The revised PowerShell script now looks like this:

cd sqlas\localhost\tabular
backup-asdatabase "rosling.abf" hans_rosling_project -ApplyCompression -ErrorVariable myError
if ($myError) {throw $myError}

If you test this code, you will see that the job now fails at the job step if the backup file already exists, which is nice.

Of course, it would be better if the script succeeds if a backup file already exists. The –AllowOverwrite parameter is the one we need:

cd sqlas\localhost\tabular
backup-asdatabase "rosling.abf" hans_rosling_project -ApplyCompression -AllowOverwrite -ErrorVariable myError
if ($myError) {throw $myError}

Now we’re done. The script fails when it’s supposed to fail, and succeeds in most cases.

Leave a Comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: