SQL Server Backup Files Cleanup


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:

Screen Shot 2016-02-23 at 8.55.04 AM.png

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:

Screen Shot 2016-02-23 at 8.55.04 AM.png

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.

VAIDY

Advertisements

One thought on “SQL Server Backup Files Cleanup

  1. Pingback: Interesting Findings & Knowledge Sharing » SQL Server Backup Files Cleanup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s