How To maintain only specific backup files in SQL server

There are many business scenarios that cause to maintain backup files for all applications and its database. In some case the organization doesn't require all the backup files that it was taken more than some days before. They need only certain days backup files, for example, they need only one week of backup files. The remaining no need to maintain. In this scenario we have an option in the SQL server called Maintenance plan.

How to achieve this in the SQL server Maintenance Plan

Step 1

Go to Start >>All programs >> SQL server Management studio. Enter a user name and password to login to the server

Note: Make sure that the user has rights to take a backup in the SQL server.

Step 2

In the left side menu Go to Management >> MaintenancePlan , right click and select New Maintenance Plan.

Step 3

You will get a drawing surface area. In the drawing surface area, drag the backup database task to it.

Step 4

Double click the backup database task in the surface area and give input as you required. In this scenario, the task going to take a full backup of all Databases and store it the specified folder also the sub-folder will be created for each database. Click OK to save the task.

 Step 5

In the drawing surface area drag the Maintenance Cleanup Task as followed.

Step 6

Double click the Maintenance Cleanup Task and set-up the task as you required. In this scenario, it is going to delete the backup files which located in the following location and the files older than one week. Click OK to save the task.

Step 7

Now, the backup task and Clean up task ready.So these tasks needs to be scheduled. Click the button in the right side corner of Subplan window and schedule the task as you required. Click OK to save the schedule.

Step 8

Now, Save and close the maintenance plan. Make sure that working properly.

Apart from this, there are some other options available that you can use based on the scenario you are required for.