SQL Maintenance Plan Doesn't Delete Backups

I recently created a maintenance plan for our server to backup all the databases on a daily basis.  In the cleanup step I set it to delete backups older than 7 day.  I let the plan run for a week and checked on it to make sure the old backups were being deleted.  This would be kind of a useless post if everything was working correctly, so as you guessed, they were not being deleted.  I check everything on the plan: Username, folder name, first level subfolders, permissions on the backup folder, sql server agent permissions.  You name it.  All to find out that my problem was in the maintenance plan all along.  The option for file extension was the issue.  I had entered .bak when I should have entered bak.  I was thinking hey, I set up a foreach loop to loop through text files I enter .txt so for backup files I should enter .bak: WRONG!  Once I changed this option from .bak to bak everything worked fine.  Now my maintenance plan backups and deletes everything older than a week!

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

16 Responses

  1. Andy says:

    haha! Nice one, thanks for sharing. I was banging my head trying to see what was wrong.

  2. Bill says:

    Here is another caveat on the backup maintenance plan I discovered. My backups would not delete and I did not have .bak in the file extension but I did have BAK in the file extension. Once I changed this to all lowercase, it worked like a charm.

  3. Don says:

    Thanks for that great hint

  4. Mark S says:

    Thank you Bradley. Super tip !

  5. sugianto says:

    i have the same problem, everything look OK, task done, log success, but not delete anything,
    your sharing fix my problem, thanks for sharing

  6. Andrew says:

    Thanks! This solved my problem.

  7. Harry says:

    Nice Bradley – now how do we get MS to put this piece of crucial information in their documentation?

  8. JR says:

    I’m having the same issue with backups not being deleted, however I am not using .bak. I’m using bak as required and old backups still do not delete. A check of the logs shows the clean up task ran without error, yet is still not deleting the old files.

  9. Steve Lee says:

    OMG!!! Thanks a ton. I have been banging my head over this for months. All because I had the “.” in there.

  10. Dave says:

    Many thanks for this, had the very same problem. Thank you for sharing 😉

  11. Kent says:

    It’s hard to believe, that this is still tripping us all up. Thanks again for sharing

  12. Tim says:

    Thank you so much! Appreciate you sharing this. Was a big help.

  13. muctarrs says:

    Awesome ..thanks.Worked a treat!

  14. J C says:

    do wildcards work in the filename?

  15. bilzin says:

    Thanks alot! its working perfectly now.

Leave a Reply