In most campaign settings magic items are made on purpose. There’s some blacksmith that forges a sword with great skill that is then infused with magic by a wizard. Sometimes they are created by the gods themselves. Evil items may be the work of evil wizards, dark gods, demons et cetera.
Recently I thought about different ways to introduce magic items into the games. In an Earthdawn game I played in many years ago, I played a weapon smith that had lost his wife and baby son in a raid on his home village. He was away at the time and when he came back, he found his family murdered. So he took one of the swords like the ones at Mini Katana he had been working on and finished it. He worked all day and night and forged his grief, desperation, anger and hate into that weapon and went out to avenge his family. He single-handedly killed the raiders and from this day on the weapon acquired some magical properties.
It was a great (perhaps a bit clichéd) story but it served its purpose. This sword was not “just another magic item” but it was my character’s grief and anger forged into steel. Alas I hadn’t have the chance to play this character for long but the GM and I intended to make the sword more powerful over time, it was supposed to get better along with the character itself.
In my opinion I should use magic items of that kind more often, provided it fits into the campaign setting. Throwing just another longsword +1 or mighty shortbow +2 at the party starts to get boring fast and all the sense of wonder becomes lost. In most campaigns magic items are just more flashy versions of the standard equipment.
Two things are important to make magic item more memorable: First they have to mean something to a player character. Even the most interesting magic item is just another thing in his backpack when it’s not relating to him. Make it a family heirloom, or something like that, to give the item and player a connection. The second thing you have to keep in mind is that the stronger the character gets the better items he needs. That’s at least true in game systems like D&D. So the item has to grow with the character. Instead of giving the player a better item, let the one he already owns evolve. Finding out more about the item could even be an entire adventure. And the reward is the activation of another power.