Introduction

I am writing about very important part of our development How to schedule backup in SQL Server. We work sometimes offline (at our local machine) or online server, our day-to-day database development backup versions are very important in our SDLC. If we are working & omitting important information from back end MS SQL Server, It pulls back us, we need put extra efforts to recover the lost time of development.  I have provided below steps how to schedule backup in SQL server?

Step 1: Open Microsoft SQL Server Management Studio to schedule a backup plan in SQL Server.
Step 2: Then expand Management > folder

Management

Step 3: Open Management > Maintenance Plans folder.

Management Plans
Step 4: Right Click on “Maintenance Plans” & context Menu contains “New Maintenance Plans…

New Maintenance Plan

Step 5: A popup window will open to write your Maintenance Plan name.

New Maintenance Plan








Step 6: Left side a toolbox window will open where you can drag & drop “Backup Database Task” option.

Maintenance Plans Task

Step 7: Now two part of window will be shown upper part have Plan Name, Schedule, and Plan Description and lower part contain “Backup Database Task Plan”.


Step 8:  Once drag & drop "Back Up Database Task", now on lower part you can see Back up database task window

Back up Database Task



Step 9: Right click on “Backup Database Task Plan” contains on lower part of window and click on “Edit” option.

Backup Database Task

Step 10: Now a new window will pop up where have different options to schedule your back up plan i.e. Connection, Backup Type, Database (Select databases as per requirements) etc.

Backup Database Task

Step 11: Now at the bottom of “Backup Database Task Plan” there is an option to check option “Create a sub-directory of each database” and we can set back plan “Folder” path.

Backup Database Task

Step 12: Upper part of Window there is a button to schedule maintenance day and time.

Maintenance Plan Task

Step 13. A new window will open with name “Job Schedule Properties”, sub plan name, Schedule Name, “Frequency” have Occurs, Recurs every, Daily frequency, Duration, Summary.

Job Schedule Properties

Step 14. You have done all steps now click on “OK” button.

Your Management studio is not showing all list of configuring option, I have given below stored procedure run in your query analyzer & will up show advanced configuration options.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Summary

If you have done carefully all above 14 steps, It will help you schedule your backup maintenance plan moreover I have provided snapshots of each item so you can understand well when you are going to implement on to your machine.

Video: How to Schedule Backup in SQL Server

Suggested Reading