Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.
4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following
6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.
7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next 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.
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.
10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish 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.
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).
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.
- Open sql server 2005 Management Studio and connect a server
- Expand the tree (left navigation) ”Management->Maintenance Plans”
- Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.
4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following
6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.
7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next 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.
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.
10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish 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.
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).
