Scheduling automated backup using SQL server 2008:
It is very important to take backups for the database files on regular basis. Microsoft SQL server 2008 has made this task very easy.
- Users can schedule the backup to be taken at a particular interval
- Users can schedule the backup copies to be deleted at a particular interval.
Do the following tasks to take a backup of a database:
1. Go to Start –> All Programs –> Microsoft Server 2008 R2 –> SQL Server Management Studio, Click SQL Server Management Studio. Microsoft Server Management Studio window with Connect to Server dialog box appears (see Image-1).
2. Enter the password of the server in Password field.
3. Click Connect. It connects to the database server and the Microsoft Server Management Studio window refreshes (see Image-2).
4. From the object explorer, make sure SQL server agent is running, if not start SQL Server Agent (Right click and press start).
5. Expand Management subfolder à Maintenance Plans. Right click on Maintenance Plans and select New maintenance plan
6. Enter a relevant maintenance plan name that suits your plan in the popup box. This will identify your backup (see Image-4). Click OK
7. Maintenance plan configuration page opens (see Image-5). Note the marked areas with arrows, these are the two areas that need to be used for setting up the maintenance plan. The marked area in the right top will be used to configure the time at which the plan needs to be executed. The bottom left pane shows the tasks that can be utilized to create a SQL Maintenance plan.
NOTE: In the Maintenance plan configuration page (see Image-5), if Tool Box window (bottom left pane) does not appear go to View –> click on Tool Box.
8. Click on the calendar item shown in the right side top. Job schedule properties window pops up (see Image-6). Configure the execution time/frequency of the tasks in this pop up as per your requirement. Usually database backups are taken on daily/weekly/Monthly basis. Make sure you are selecting proper time so that your databases is least used. Click OK once you finish.
9. From the maintenance plan tasks pane ( Image-5), on the left side, select and double click on the Back Up Database Task, this will be used to select the databases to be backed up and the location where to save the Backup files (see Image-7).
10. Double click on the Back Up Database Task, it will open a new window that allows you to configure the database configuration for the backup. Here you configure the databases that you need to backup, then specify a location for the backup, the extension for the backup files etc. (see Image-8).
From the pop up modal window, by clicking on “Databases” drop down, you will be able to select the required databases and also configure the file location, extension for the backup file etc.
11. Click OK once finished. Now Backup plan configuration is over. The backup files will be created on the scheduled time in the specified folder. The name of the file will be created by appending the date so that you can identify the back up for a particular date.
Since the backup files are created frequently, it is a good practice that you delete backup files after a certain period of time. For this you need to execute clean up task along with the maintenance plan. You can configure the clean-up task as follows.
From the left side pane, drag and drop maintenance clean-up task (see Image-9).
12. Once you click OK, then save the maintenance plan. You can either wait till the next execution time or execute it manually in order to check whether everything is working fine.
source: Microsoft, sreejukg
21 thoughts on “Scheduling automated backup using SQL server 2008”
Hey! I could have sworn I’ve been to this blog before but after checking through some of the post I realized it’s
new to me. Anyways, I’m definitely delighted I found it and I’ll be bookmarking and checking back frequently!
Thanks Madhu sir, you saved my day, i am expecting more like monitoring tools and network related tasks.
I cant find sql server agent
you have to install SQL agent separately or you have to download and install SQL Express Edition with tools
thank you for such visual explanation
Thank you Paresh
hi Madhu..i did your whole process but not working in my local database server .:( plz suggest me if u hv any idea regarding this
Sorry for the late reply. After the completion of the configuration, please execute manually and check backup is happening or not.
Check all the SQL services are running or not, especially SQL Server Agent.
Please let me know if you need any help from my side.
I did as you said, but I don’t see back up file . Error like that in Log file ” Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE ReportServerTempDB WITH DIFFERENTIAL. Check the backup application log for detailed messages.
Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the command BACKUP DATABASE Testing WITH DIFFERENTIAL. Check the backup application log for detailed messages.
.i did your whole process but not working in my local database server.
i already checked all the SQL services are running perfectly but its not working please suggest me any solution fir this..
Very useful Tutorial
Hi,Thank you so much for this article.You have explained all the steps very clearly with images.keep up the good work.
it came up the right time i needed it!!! this article is fantastic, simple and informative. thanks a lot.
I am now not sure where you’re getting your info, however great topic.
I must spend some time finding out much more or understanding more.
Thank you for wonderful information I was looking for this info for my mission.
Hi there! I realize this is somewhat off-topic however I needed to
ask. Does managing a well-established blog such as yours take a large amount of work?
I am completely new to writing a blog however I do write in my journal on a daily
basis. I’d like to start a blog so I will be able to share my
personal experience and views online. Please let me
know if you have any kind of suggestions or tips for new aspiring bloggers.