Sunday, April 18, 2010

How To Create A SQL Server Maintenance Plan Using Management Studio

In SQL Server one of the best feature is Maintenance Plan, where you can schedule hourly/daily/weekly/monthly backup, restore, rebuild index job and many more. Here I show you how to schedule daily back Job through maintenance Plan.

Step 1. Open SQL Server 2008 or 2005 Management Studio and connect to a server

Step 2. Expand the tree (left navigation) Management

Step 3.
Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

Step 4. Follow the wizard

Step 5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following.

Step 6. Once you click the button “Change” you will get a new popup to enter schedule details as shown below.

Step 7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.

Step 8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

Step 9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

Step 10. From the above window, browse the folder location where to store the backup files.

Step 11. Continue the the steps until you get to Finish step.

Step 12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.

Step 13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).
how to delete database maintenance plan, how to configure maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2012. SQL Server 2005 maintenance plan.


Post a Comment