Creating a Simple SQL Maintenance Plan

In this article, I will discuss the steps on how to create a simple maintenance plan in SQL that will backup both database and transaction logs.

Here are the steps to follow:

1. Right click Maintenance Plan >> Select New Maintenance Plan.



























2. Drag a Back up Database Task from toolbox.























3. Double click Back up Database Task to set the properties. Set Backup type to FULL.









































4. Add another Back Up Database Task and rename it to Back Up Transaction Log Task.









































5. Double click Backup Transaction Log Task to set properties. Set Backup Type to Transaction Log.

In the steps mentioned above, we did not shrink the .ldf file but rather we simply backup the transaction log after full database back up. The effect of backing up the transaction log tells SQL Server that it is safe to reuse the space consumed by the transaction log. So if the size of transaction log at that time is 300 MB, SQL will reuse the 300 MB space to record new transactions instead of consuming more hard disk space.

As a finishing touch you can add a File Maintenance Task to delete the backup files in server. Note: If it is production I hope you back up your database somewhere before you delete the backup in your server.