I learned something interesting today. Something so simple yet crucial. Alright, let me jump straight on to it.
After going live with GP2015R2, as part of SQL maintenance tasks, I created daily backup schedule in SQL Server. Client’s backup retention policy is 3 prior days. So I created a “Maintenance Cleanup Task” to remove all backup files that are older than 3 days.
All good. Only that, it didn’t work. And we realised that only yesterday. Bummer. Thankfully, it was a new server so we had plenty of space. And size of DBs are quite small. So I could see backup files dating yesterday. Which is nice.
I had to figure out what’s going wrong all this time. It’s a fairly simple setup. Nothing complex. Something must be wrong. I opened the maintenance task definition window. Shown below:
Looks perfectly alright to me. Isn’t it? But nope. Something makes this task fail every night.
When I looked at this post, Maintenance Cleanup Task (Maintenance Plan), on Microsoft MSDN site, I could not get much help from. It was good, detailed, but not good enough to help me why my task fails every night.
I then did something that I thought is weird, but turned out to be the solution.
I changed the File extension value from .bak to just bak, as shown below:
Saved the definition and ran the task manually to check if it worked.
Voila… It worked…!!!
Those who are reading this and thinking about why I am blogging something as simple as this (as if I found the god particle), well in all honesty, I had absolutely no idea about this till now. Just thought I would share this, as I always do. So please be nice on me.
Pingback: Interesting Findings & Knowledge Sharing » SQL Server Backup Files Cleanup