Error handling / Output (see warnings for details. )

[tovoro]

How do you people handle errors within the module?

I would like the people who run the ansible job to see what the exact error is, but cant seem to find out where the message is returned exactly:

For example:
When I try to restore a DB that already exists (with_replace set to false), the module outputs:
"Error restoring database: Database Testdb unable to be restored, see warnings for details."

How can I access the "warnings" within the ansible playbook?

Thanks for your help

[lowlydba]

That particular error message is defined upstream by the dbatools pwsh module. They have made the choice to put the details as a warning log, and just have the error message tell you to refer back to them.

I'm not positive if this will work, but have you tried registering the output and then printing stderr? Something like:

 - name: Create a database
      lowlydba.sqlserver.database:
        database: "x"
      register: result
- name: Display stderr
      debug:
        msg: "{{ result.stderr }}"

If that doesn't work, then I think it'd mean it would take an enhancement to explicitly capture and display warning messages overall.

@briantist Any thoughts?

[briantist]

stderr isn't an automatic return field for modules, so I don't think it will exist unless you've implemented it. The module will have to capture the warnings and return them I think.

[tovoro]

So, if i Understand correctly, theres no way to get the error details within ansible right now?
It would be great, if this could be improved if possible.

Im struggling to find a reliable, idempotent way to run mssql scripts (lets say create an audit and audit specs for example), with ansible, any hint or help would be highly appreciated

[lowlydba]

I can take a look at adding that for the restore module, I think that's the only place that would need it as most other dbatools stuff contains the context in the error message itself.

In the meantime, you could have a database task before the restore that runs in check mode to validate if the database already exists that you're restoring to, and error out if the check mode task returns unchanged (meaning it already exists). You can find a similar task in the database module test playbook.

[lowlydba]

Thanks @briantist ! 🙏🏻

[lowlydba]

@tovoro This will be resolved in version 2.3.4 (released!)

The log output for the restore command will now include warnings when it fails:

fatal: [runner]: FAILED! => {"changed": false, "failed_when_result": true, "msg": "Error restoring database: Database model_restore unable to be restored, see warnings for details. Additional warnings: [17:24:42][Test-DbaBackupInformation] Database model_restore exists, so WithReplace must be specified [17:24:42][Test-DbaBackupInformation] File C:\\Program Files\\Microsoft SQL Server\\MSSQL16.MSSQLSERVER\\MSSQL\\DATA\\db_model_restore_new.mdf already exists on fv-az731-605 and WithReplace not specified, cannot restore [17:24:42][Test-DbaBackupInformation] File C:\\Program Files\\Microsoft SQL Server\\MSSQL16.MSSQLSERVER\\MSSQL\\DATA\\db_model_restorelog_new.ldf already exists on fv-az731-605 and WithReplace not specified, cannot restore [17:24:42][Restore-DbaDatabase] Database model_restore unable to be restored, see warnings for details."}

[tovoro]

Wow thanks a lot @lowlydba 🙌