How to Schedule Weekly Automatic Backup in SQL Server 2012, 2014 and 2016

Schedule Automatic Backup in SQL Server 2012

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.