Database backup job steps

Question

Database backup job steps

Pete Bishop

Default port

Points: 1498
More actions
December 29, 2025 at 9:50 am

#4708952
I have a SQL Agent job for backing up a set of Analysis Services databases with each database covered by a different "SQL Server Analysis Services Command" job step of the form:
```
{
  "backup": {
    "database": "CUBE NAME",
    "file": "CUBE NAME.abf",
    "allowOverwrite": false,
    "applyCompression": true
  }
}
```
However, if a database is added, deleted or renamed the job fails the particular step - which could result in a database not being backed up.
Is there a way to write this Agent job so that it can dynamically manage the database names?
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

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

frederico_fonseca SSCoach Points: 16292 More actions · Answer 1

use a powershell script instead - you can get all db names, and then do backup https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-asdatabase?view=sqlserver-ps

one other possible option is to have a job that updates the xmla of the backup job to include all databases (or to add a new step) and then starts that job

This reply was modified 21 hours, 59 minutes ago by frederico_fonseca.
This reply was modified 21 hours, 59 minutes ago by frederico_fonseca.

Steve Jones - SSC Editor SSC Guru Points: 740318 More actions · Answer 2

Need to see more code to know what might work, but PoSh is often easier for programming some stuff than T-SQL, especially looping type constructs.

frederico_fonseca SSCoach Points: 16292 More actions · Answer 3

check https://sqljana.wordpress.com/2019/03/08/ssas-automate-ssas-backups-with-powershell/