Creating a Scheduled Database Backup Plan using SQL Agent Jobs

This article explained that how to automate database backup process using SQL Agent Jobs

Step 1: GOTO the machine on which SQL Server is installed.

Step 2: Connect to the SQL server using SQL Server Management Studio. You need administrative access so use Windows credentials or the SA account

Step 3: Expand the Management section from the Object Explorer pane and right click on the Maintenance Plans

Step 4: Right-click on Maintenance Plans and choose New Maintenance Plan. Provide a valid name and click OK

Step 5: Double click on Subplan_1

Name: A simple name for the section of the plan (e.g. Backup)

Description: Provide some more detail explanation (e.g. Takes a daily backup and deletes the one created the previous day)

Step 6: Click on the calendar icon next to the Schedule field. Change the frequency according to requirement.

Step 7: On the left-hand (Below the Object Explorer) there will be a toolbox containing call the available tasks

 

Step 8: Add a Back Up Database Task. You can either double click it, or drag and drop it into the beige field

Step 9: Double click on the Icon to set up the backup specifications

Step 10: Specify the databases to back up. You can click on “All user databases“, or select specific ones using the “These Databases” option.

Click OK

Select the Create a backup file for every database option

Check the box that says “Create a sub-directory for each database“

Select a location for the backup files.

Hit OK

Please leave your comments or share this tip if it’s useful for you.