SQL Server Portal

Earlier, I have written an article about how to take a backup automatically in place of manual backup. I received very good feedback about this post.

Pre-requisite :

• How to schedule automated back up

Problem :

The problem is, once automatic backup would be scheduled, it will start piling up the back up in the storage and soon, you will be out of space. In this case, you delete the backup manually or automatically ? I prefer automatically. But how to configure it ?

Solution :
Let me explain the solution step by step :

Step 1 :
It would be the best, if you select the “Maintenance Cleanup Task” in Step 6 in the Pre-requisite article. However if you did not do it there, you can modify the same maintenance plan and do it.
First of all you need to select the same Maintenance plan that you have created in the earlier article and right click on it, in order to modify it, as shown below.

Step 2 :
Once you select modify, SQL Server Management Studio (SSMS) will take you to the design screen of the maintenance plan. Now you need to select “Maintenance Cleanup Task” from the toolbar available in the left hand and drag and drop it on the right hand design window as shown below.

Step 3 :
Once you drag and drop the “Maintenance Cleanup Task” in the design window of the maintenance screen on the right hand side, you need to select the backup up task. Once you select it, it will show a green arrow in the bottom. Just drag that green arrow and drop it at the head of “Maintenance Cleanup Task” as shown below. The reason behind to drag and drop the green arrow is, to develop a link between both task. In addition to this, this link will define the sequence as well, so that the backup process will be taken first and then cleanup task, in order to remove the old backup.

Step 4 :
It seems that configuration is done. However, I can see a red cross sign on “Maintenance Cleanup Task” and the reason behind it that we did not configure the “Maintenance Cleanup Task”.
So lets configure it. In order to configure it, you need to double click on “Maintenance Cleanup Task”, you will get “Maintenance Cleanup Task” property window. Here most of the stuff is self explanatory. However, bear in mind that extension must NOT be with “dot”. Generally it is “bak”, in order to delete backup files ONLY as shown below.

Step 5 :
Now, you are done with the configuration, just save the maintenance plan and close it. Now it will automatically take the backup and delete the older files defined in step 4.

Let me know, how you clean your older backup files.