I need to retrieve a message deleted in error

If you are syncing messages with iCloud, you cannot recover it. If you are including messages in your backups, you can recover it by restoring the backup, as long as it is in the backup. When you restore a backup. You overwrite the contents of your device so you may be turning the clock back on your device quite a bit if the backup is quite old.

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