Maintenance Plans in SQL Server

Creating backups for your database is an essential task in every environment especially for critical data storage. But have you ever ask yourself how to create your backups in a periodic manner - say every week or so? If you was able to do so, how will you manage the space in your disk? Maybe you don't want to keep any backup files older than one month.

The first solution you may think about is to create a SQL script and execute it in a custom job. But wait, SQL Server can do it for you and save alot of this implementation hassle. In the SQL Server, you have the option to create "Maintenance Plan" for your database. This plan provides you with more advanced options for creating backups for the data and the transactions logs.

When you create a new plan, you will have to go through a configuration wizard. The interesting part is in the backup configurations. You can provide how often you want to backup your database (i.e every week, month or so). You also specify how long you want to keep the backup files and when to remove it (i.e You may remove any backups older than one month) so that you save your storage space. Another interesting point, you can create only one plan for more than one database. The plan can be configured also to create a separate backup folder for each database.

The maintenance plan is a periodic task which needs a running job. This is why it requires a running SQL Server Agent in your database server.