How to retrieve a deleted message?

If you are using Messages in iCloud, once you delete a message, it's gone. It's not part of any backup.

If you are not using Messages in iCloud and you have a backup that contains the messages, you can restore your phone from that backup:

Restore your iPhone, iPad, or iPod touch from a backup - Apple Support

Note that restoring the phone from a backup will overwrite everything currently on the phone.