Often players expect their DMs to provide motivation for an adventure. This is not fair. DMs have a lot on their plates, and while they almost certainly have some idea of what the grand scale story is going to be, a good DM will have accounted for backstories and motivators affecting the PCs outside of the main plot. So, how do you create a character that is motivated?
The question that must always be answered when creating a character backstory is "why is my character an adventurer?" Your backstory will answer this question, and should absolutely include something which keeps your character moving. Memory loss, vengeance, the hunt for a rare item, love, all of these are good motivators for characters. However, to turn the general motive that is in your backstory into actionable items, you need something else.
Get as specific as you possibly can. You don't just want vengeance against the man who killed your father, you want vengeance against Gerald Horningfather, aid to the king and one of the most powerful men in the nation, who killed your father over a petty land dispute. You're looking for powerful poisons, ways to get into the castle unnoticed, wicked weapons and a team willing to do something bad for the greater good so that you can enact sweet vengeance.
3. Look at the Long Term
Having a specific goal pulled from your compelling backstory should give you a specific long-term goal. In general, having a long term goal is good because in any given "big decision" moment you can pull from this big goal to make said decision. It also gives your DM tons of interesting character and story development options to pepper into the campaign that will further invest you.
4. Look at Short Term
People tend to know what they want to do in any given moment. So should your character. The easiest way to do this is to draw from your Long Term and consider what your character can be doing on the day to day to get closer to that long term goal. They want to become king? Well that's great but what are they going to wake up and do about it right now? Probably try to gather some advisors, maybe learn about politics, or ally themselves with a powerful leader somehow. They want to find love? Well they're probably looking for gold to get a tailored suit, or hitting up local taverns, or looking for a great adventure to brag about later. If the long term is specific, then it makes the short term easier to puzzle out. Your character loves the prince of a faraway land who loves lilies. Well, now your character needs three things: lilies, a way of preserving lilies, and a way to get to that faraway land.
5. Allow for Change
Characters who want the same thing and relentlessly pursue it for a whole campaign get exceptionally tedious. That's not to say a character must abandon their motivation, but people are complicated and it's okay to incorporate new motivators, change perspective on your current motivator, or become increasingly more fixated and fanatical to the point that you're hardly recognizable despite only pursuing one motivation. Allow for your character to change, and new paths will open up.
6. Communicate Your Motivators
Any relationship needs proper communication. This includes romantic ones as well as DM/Player relationships and player/player relationships. Your DM cannot properly utilize all of the cool specific details you use to keep your character moving in the plot if you don't make those details clear to them. Similarly, if your character wants to suddenly deviate from the main plot to pursue their own goals, their party should be aware of those goals in some way so that everyone can stay together or separate in a way that makes sense.
All in all a campaign doesn't revolve around one player or their motivators, but motivators are still important. Players have a great amount of agency in DnD, and it's fun to be able to use that agency to your advantage. Don't forget to keep your characters interesting and moving forward.