Schedule Automatic Backup in SQL Server 2012 – Part1

You know it is very important to back up your SQL server database backup on the regular basis. So in this post, we will see how to schedule automatic backup in SQL server 2012.

We will go step by step to schedule so that we all can understand each and every step clearly. Before scheduling automatic backup we have make sure the following two points:

• User should have the SYSADMIN privileges to schedule automatic backup.
• SQL Server Agent service should be running.

 Schedule Automatic Backup

• Click on the Start and Navigate to SQL Server Management Studio

• Login to SQL Server Management Studio with User having SYSADMIN privileges.
• Navigate to Management Node from the object explorer, and then select the Maintenance Plans node.

• Right click the Maintenance Plan and then select New Maintenance Plan.

• Enter the Name of the New Maintenance Plan

• Now the configuration page will appear for Maintenance Plan

• Now click on the Calendar Item as shown in the above image (on Top) and following screen pop up. Now in this screen we can configure Schedule as per requirement and click OK.

• From the Maintenance Plan Tasks tool box pane in the left side, select the Back Up Database Task. Drag and drop backup database task to the right side or just double click on it.

• After that right click on backup database task and click Edit it will allows you to configure the database configuration for the backup. Here you configure the databases that you need to backup, then specify a location for the backup, specify the extension for the backup files as per requirement.

• Click OK to finish configuration. Now backup plan configured. The backup files will be created on the scheduled time to the mentioned folder. The name of the file will be created by appending the date so that you can identify the back up for a particular date.

Click here for Schedule Automatic Backup in SQL Server 2012 – Part2