IntroductionI 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;
sp_configure 'Agent XPs', 1;