The old covenant which God made with Israel through Moses was very different from what He made through Jesus. In that, God's blessings were based on how people behaved (De.28). What God was operating on in this covenant was His righteousness, even though He didn't stop being merciful and gracious in an overall sense. We see how Moses who had risen to great heights of leadership and closeness with God was penalised for dishonouring God by disobeying Him (Nu.20:8-12). Elijah who exalted God's name over Baal at Carmel ran in fear of the wicked queen. That virtually marked the end of his ministry which got handed over to Elisha. These two men of God did not lose their salvation, and God gave them their due respect afterwards. But they lost what they could have been if they had not failed at that part of their life and ministry. There are Joseph and Daniel, on the other hand, who had a clean record till the end.
But we see God working differently in the new covenant. His promise is that He will not remember our sins any more (He.8:12) and that He will be with us always (13:5). The example we see is Peter who denied His Lord three times but was so restored to Him that he lived to lead the inauguration of the church on the Day of Pentecost, open the Gospel to the Gentiles and write two books of the New Testament. God is still righteous, but He operates the new covenant based on His grace.
Grace does not mean that we can fail and then things will go on as before. We may face discipline (12:6), but He will not leave or forsake us. But we must remember one important point. If we fall, we must acknowledge it, confess it and repent from it. This is not just to be from our mouth (1Jn.1:9), but repentance must come from our heart and be seen in our actions (2Co.7:10,11). That was what Peter did, when he realised what he had done. If we take it for granted that God forgives, that may be the reason why there is no real restoration with God.
There is no record or Moses or Elijah having repented like that. In fact, Moses seems not to have realised ever what he had done wrong, because he thought it was the people's fault (De.4:21).
It may be that after we have messed up something in our life, we keep looking at other people, how they reacted unfairly, misunderstood us, were not merciful to us, etc., and we fail to see how we have grieved the Holy Spirit by our behaviour and dishonoured God. We think we have acknowledged our sins (perhaps after getting caught), but we may not have humbled ourselves before the Lord from our heart. We may be still justifying ourselves secretly in our heart by blaming someone else who provoked us or some situation in which we were caught. That is no genuine repentance!
But whenever there is genuine repentance, there is great rejoicing in heaven. There is restoration, a promise of never to remember that sin, and a reassurance of God' love and mercy and His presence. There is a second chance with God.