How to Schedule Backup in SQL Server ?

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

Step 3: Open Management > Maintenance Plans folder.


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

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










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

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

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

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.

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.

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

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.

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

• MVC ASP.Net Interview Questions And Answers
• How to Disable Automatic Caching in ASP.Net MVC
• Cascading Dropdown list in MVC
• Jquery Timer Countdown in MVC Application