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 made this task very easy. In this blog, I am going through step by step that will allow

  • Users to schedule the backup to be taken on a particular interval
  • Delete the backup copies after a certain period of time

Schedule the database backup

First I am going to tell you the steps required to schedule the backup. Login to Sql Management studio and connect to the required database. Now from the object explorer, make sure SQL server agent is running, if not start SQL server agent(Right click and press start).

 

1

Expand the Management Node from the object explorer, and then select the maintenance plan node. To schedule maintenance plan, you need to have “SYSADMIN” database role. If you dont see the maintenance node, make sure you have the necessary permission. 

2

 

Right click the maintenance plan and then select “new maintenance plan”.

3

Enter the maintenance plan name in the popup box (This can be any name that identifies your task for ). This will identify your backup plan and you should choose a relevant name that suits your plan.

 

4

 

Now you will be in the configuration page for the maintenance plan. . Note the marked area, these are the two areas you need to use for setting up the maintenance plan. The marked area in the right top will be used to configure the time that the plan executes. Choose a time so that the database is least used. The bottom left pane shows the tasks that can be utilized to create an sql maintenance plan. since explaining all of them is not in the scope of this document, I am going to explore only two of them.

5

 

Click on the calendar item shown in the right side top. This will bring the job schedule properties popup window that configure the execution time/frequency of the tasks. Configure the data carefully so that it suits your requirement. Usually database backups are taken daily basis. Make sure you are selecting proper time so that your database is least used. Click ok once you finish.

 

6

From the maintenance plan tasks pane in the left side, select the backup database plan, this will be used to take backups for the databases. Drag and drop backup database task to the right side(as shown in the diagram).

 

7.1

 

Double click on the backup database task, it will open up 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, specify the extension for the backup files etc.

From the pop up modal window, by clicking on “Databases” dropdown, you will be able to select the required databases. Also configure the file location, extension for the backup file etc.

 

9

 

Click ok once finished. Now backup plan configuration is over. The backup files will be created on the scheduled time to the mentioned 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 cleanup task.

11

 

Double click on the dropped item inorder to edit the clean up properties. Here you need to specify the backup location, and file extension for the back up files and specify the age of the file. It is a good practice that you keep one month old data, and delete anything prior to one month. 

13

 

Once you click ok, then save the maintenance plan. You can either wait till the next execution time or execute it manually inorder to check whether everything is working fine.

Hope this helps.

Published Wednesday, January 20, 2010 3:19 PM by sreejukg

Comments

# re: Scheduling automated backup using SQL server 2008

Sunday, April 18, 2010 6:24 AM by Philip

I was configuring the backup for the first time. I just followed your steps and thats it. Thanks mate

# re: Scheduling automated backup using SQL server 2008

Tuesday, September 21, 2010 6:23 AM by Rohan

I followed the procedure and I scheduled backup and it worked fine. Can you tell me how to set local sql server to do the same and get backup from remote sql server ? I mean I want to store backup copies on local computer instead of server. What needs to be done for that ? Thanks in advance

# re: Scheduling automated backup using SQL server 2008

Friday, October 1, 2010 1:31 AM by sreejukg

You can use integration services and use import/export options available. In prior versions, it was DTS.

refer this

msdn.microsoft.com/.../ms140052.aspx

# re: Scheduling automated backup using SQL server 2008

Saturday, November 27, 2010 7:51 AM by junedh ummer

its really a clear pic which helped lot

# re: Scheduling automated backup using SQL server 2008

Thursday, January 27, 2011 4:20 AM by Steve Thompson

Many thanks. Clear and concise. Works a treat.

# re: Scheduling automated backup using SQL server 2008

Thursday, February 3, 2011 6:47 AM by M

crystal clear...really helpful

# re: Scheduling automated backup using SQL server 2008

Wednesday, February 23, 2011 7:56 AM by Satish

Great.Very Clear.Nice Post Sreeju.

# re: Scheduling automated backup using SQL server 2008

Monday, March 21, 2011 11:41 PM by Jocelyn Munroe

Thanks, great instructions. Easy to follow and implement. Much appreciated.

# re: Scheduling automated backup using SQL server 2008

Friday, May 6, 2011 1:18 AM by Azhar

this doesnot work on windows server 2008 does firewall stop the utility?

# re: Scheduling automated backup using SQL server 2008

Thursday, May 12, 2011 1:57 AM by Mohammed Zubin

But it has a problem sometimes that schedule time its not working

# re: Scheduling automated backup using SQL server 2008

Wednesday, May 25, 2011 8:14 AM by Girish

It can't be more precise than this... thanks for sharing the info

# re: Scheduling automated backup using SQL server 2008

Thursday, May 26, 2011 1:12 PM by Shahid Khan

After reading all comments found Azhar has issue on windows 2008. I did setup schedule on windows 2008 and have no issue. BTW my firewall is turned off.

# re: Scheduling automated backup using SQL server 2008

Monday, May 30, 2011 4:16 AM by jay

thanks a lot it's a big help

# re: Scheduling automated backup using SQL server 2008

Monday, June 6, 2011 6:32 AM by Sam

Thanks a lot, it helps.

# re: Scheduling automated backup using SQL server 2008

Monday, June 6, 2011 2:40 PM by nagendra

nice job...thanks

# re: Scheduling automated backup using SQL server 2008

Tuesday, June 7, 2011 10:43 AM by Diane

What is the difference between this and using maintenance plan and maintenance plan wizard under ssms to create backup jobs?  Thanks

# re: Scheduling automated backup using SQL server 2008

Thursday, June 16, 2011 10:58 AM by snap

What is the difference between this and using maintenance plan and maintenance plan wizard under and Sql Server Agent ? thanks

# re: Scheduling automated backup using SQL server 2008

Friday, June 17, 2011 10:53 AM by Tony

THX!

does the job

# re: Scheduling automated backup using SQL server 2008

Monday, June 27, 2011 3:29 AM by Vidya Ramesh

Very good document and usefull too. Thanks.

# re: Scheduling automated backup using SQL server 2008

Tuesday, July 12, 2011 3:26 PM by Babatunde tunslike

Many many many thanks for a well concise and instructed article.

God bless u.

# re: Scheduling automated backup using SQL server 2008

Thursday, July 14, 2011 10:46 AM by lynn

thanks! worked great!

# re: Scheduling automated backup using SQL server 2008

Friday, July 15, 2011 12:38 AM by P.B

Thank you very much for nice paper

# re: Scheduling automated backup using SQL server 2008

Tuesday, July 26, 2011 10:03 PM by Richard Torrenueva

so cool..nice one..keep it up....

# re: Scheduling automated backup using SQL server 2008

Friday, August 26, 2011 3:34 AM by Daniel Francis

i have done everything as explained by this good piece of advice. but i am still getting catastrophic failure when i want save my configuration, please assist me on where i am missing?

# re: Scheduling automated backup using SQL server 2008

Friday, September 2, 2011 12:15 PM by Wes

My knowledge of SQL is only knowing how to spell it.  Had to get backups of the BES database, this document was concise and worked perfectly.  Many thanks to the author.

# re: Scheduling automated backup using SQL server 2008

Monday, September 12, 2011 11:58 AM by Alba_Jock

Fantastic, simple, it just works! Thanks, all the training books/videos I have only tell you how to script a backup, no GUI instructions. You've saved my data and countless hours banging my head against the wall. Cheers!

# re: Scheduling automated backup using SQL server 2008

Tuesday, October 4, 2011 1:45 AM by Subhash S Kewat

Thnx Lot

# re: Scheduling automated backup using SQL server 2008

Tuesday, October 4, 2011 7:26 AM by Mogudoom Mohamed

you done a nice job

thank you very much

it is more helpful to me

# re: Scheduling automated backup using SQL server 2008

Wednesday, October 12, 2011 3:39 AM by Pragash

thanks a lot, its helped me a lot

# re: Scheduling automated backup using SQL server 2008

Thursday, October 13, 2011 4:51 AM by Girish

Thanks a lot its clear instruction; even Novice can also understand.

Can we make scheduling separetly for database and log files

# re: Scheduling automated backup using SQL server 2008

Wednesday, October 19, 2011 12:44 PM by Jay Gajarao

Thanks a lot, nice article

# re: Scheduling automated backup using SQL server 2008

Sunday, October 23, 2011 5:35 PM by Thileepkanth

Thank you Very much..sreejukg.

I have solved my problem without any doubt. again thanks a lot.

# re: Scheduling automated backup using SQL server 2008

Friday, October 28, 2011 10:16 AM by Fernando França

Thanks for the tutorial!

# re: Scheduling automated backup using SQL server 2008

Saturday, November 12, 2011 2:29 PM by Roberto

Te agradesco por la información

# re: Scheduling automated backup using SQL server 2008

Wednesday, November 16, 2011 5:50 PM by budi99

Great Tutorial..

Thanks..

# re: Scheduling automated backup using SQL server 2008

Friday, November 18, 2011 6:54 AM by john

very usefull tutorial. thanks boy.

# re: Scheduling automated backup using SQL server 2008

Thursday, November 24, 2011 7:10 AM by Shantanu

Very Good !!! Thanks Sreeju

# re: Scheduling automated backup using SQL server 2008

Saturday, December 3, 2011 1:34 AM by Anita

Completely understandable

# re: Scheduling automated backup using SQL server 2008

Saturday, December 3, 2011 1:35 AM by Anita Sant

Very much useful,great

# re: Scheduling automated backup using SQL server 2008

Saturday, December 10, 2011 2:49 AM by anita.s

i didn't get maintenance plan option in 2008 on pc??

how i seen that??

# re: Scheduling automated backup using SQL server 2008

Thursday, January 5, 2012 9:52 AM by Roshoo

i have done everything as explained by this good piece of advice.

Thanks

# re: Scheduling automated backup using SQL server 2008

Friday, January 6, 2012 8:31 AM by Jack

Clear, concise, and explained perfectly.....THANKS!

# re: Scheduling automated backup using SQL server 2008

Tuesday, January 17, 2012 6:32 AM by Radas

GREAT- That works perfectly.

Thank you

# re: Scheduling automated backup using SQL server 2008

Monday, January 23, 2012 6:09 AM by Vishal

Thank you so much for such a clear and concise guide!

# re: Scheduling automated backup using SQL server 2008

Friday, January 27, 2012 1:36 AM by Prabu

Thanks for your tfs restore Tutorial..I have some queries related tfs bcp plan.i had completed the all activities on tfs restore.Hence the share point and reporting  part some issues.show portal page getting error Please help me

My mail id :prabhu4d@cspl.com

Thanks

# re: Scheduling automated backup using SQL server 2008

Wednesday, February 1, 2012 3:33 AM by h4ck3r8ug5

Much appreciated bud. Good tut

# re: Scheduling automated backup using SQL server 2008

Thursday, February 2, 2012 8:18 AM by Muhammad Haroon

Excellent way to convince and share your knowledge. Keep it up

# re: Scheduling automated backup using SQL server 2008

Friday, February 3, 2012 1:52 PM by bing25

Thank you so much...it was very helpful!!

# re: Scheduling automated backup using SQL server 2008

Monday, February 6, 2012 6:15 AM by Mahbod

tnx a lot , i have to kiss you because it was my first try by following your steps , GoodLuck

# re: Scheduling automated backup using SQL server 2008

Wednesday, February 8, 2012 3:10 AM by Behzad

Thanks a lot

# re: Scheduling automated backup using SQL server 2008

Wednesday, February 8, 2012 8:42 AM by bilgin

very very good

# re: Scheduling automated backup using SQL server 2008

Thursday, February 9, 2012 4:04 AM by manish Kumar

Hiiii sir

how r u?

thanks a lot ...it is very helpful and understandable.

# re: Scheduling automated backup using SQL server 2008

Tuesday, February 14, 2012 7:00 AM by patan dadapeer

Hi sir, i want to know what are the initial steps to make database recovery in the sql server...... please mail me.. dadapeer.patan8817@gmail.com

# re: Scheduling automated backup using SQL server 2008

Wednesday, February 15, 2012 9:10 AM by Amishmiser

Perfect! God bless you. your a life saver!

# re: Scheduling automated backup using SQL server 2008

Saturday, February 18, 2012 11:50 PM by Vasker

Nice

# re: Scheduling automated backup using SQL server 2008

Monday, February 27, 2012 1:30 AM by madhu

Excellent but in same maintenance window can't we create full,differential backups

# re: Scheduling automated backup using SQL server 2008

Monday, February 27, 2012 5:29 AM by Jahn L.

Thx for this! Really helped me a lot.

# re: Scheduling automated backup using SQL server 2008

Friday, March 2, 2012 2:46 PM by Mary

Well done.

# re: Scheduling automated backup using SQL server 2008

Tuesday, March 6, 2012 4:23 AM by shibu

thanks! worked great!

# re: Scheduling automated backup using SQL server 2008

Friday, March 9, 2012 9:27 AM by fabrice

worked for me as well!

Thank you so much for that topic that made me save my time :-)

# re: Scheduling automated backup using SQL server 2008

Tuesday, March 20, 2012 4:16 AM by jun gomez

thanks.. was a huge help

# re: Scheduling automated backup using SQL server 2008

Tuesday, March 20, 2012 5:23 PM by cWulSVPaGTMyKvUAzi

I really liked your post.Much thanks again. Really Cool.

# re: Scheduling automated backup using SQL server 2008

Wednesday, March 21, 2012 2:38 AM by umashankar

Thank you so much, Great Tutorial.

# re: Scheduling automated backup using SQL server 2008

Wednesday, March 21, 2012 5:42 AM by Botond

Great thank you!

# re: Scheduling automated backup using SQL server 2008

Sunday, March 25, 2012 4:09 PM by Adrian

Really nice, well done, God bless you!

# re: Scheduling automated backup using SQL server 2008

Monday, March 26, 2012 3:48 AM by Lalit

Thanks very much

# re: Scheduling automated backup using SQL server 2008

Wednesday, March 28, 2012 2:36 PM by mk

Great posts.. very clear..

one quick ask - how do you manually execute the task after maintenance plan is created???  thanks

# re: Scheduling automated backup using SQL server 2008

Friday, March 30, 2012 12:56 AM by eb5 visa

worked for me as well!

Thank you so much for that topic that made me save my time :-)

# re: Scheduling automated backup using SQL server 2008

Monday, April 2, 2012 3:07 PM by Sushil Kumar Pandey

Nicely define and easy to get.

# re: Scheduling automated backup using SQL server 2008

Monday, April 9, 2012 7:26 AM by Bhanu prakash

Great post.. very clear..

# re: Scheduling automated backup using SQL server 2008

Thursday, April 12, 2012 10:47 AM by Michael

Spot on !! cheers

# re: Scheduling automated backup using SQL server 2008

Friday, April 20, 2012 7:37 AM by Aniruddh

it is gud job , very easy to understand,worked for me

# re: Scheduling automated backup using SQL server 2008

Friday, April 20, 2012 7:39 AM by Aniruddh singh

thanks for this very useful post.

# re: Scheduling automated backup using SQL server 2008

Monday, April 23, 2012 3:51 AM by DKshah

Its Really a Good One ...

I had completed With automated backup with Help of This Doc..

Thank You

# re: Scheduling automated backup using SQL server 2008

Thursday, April 26, 2012 1:20 PM by Dude

Thanks a lot for the instructions...very clear and helpful.

# re: Scheduling automated backup using SQL server 2008

Thursday, May 3, 2012 2:21 AM by Anish Chapagain

It have given me, complete view of maintenance plans usage in SQL server along with easy Backup facility.

Thanks a lot for such descriptive instructions.

# re: Scheduling automated backup using SQL server 2008

Monday, May 7, 2012 4:03 PM by Amit

Awesome!!! Thanks!

# re: Scheduling automated backup using SQL server 2008

Tuesday, May 15, 2012 12:53 AM by Hari Vikram

Very use full for me.. Thanks a lot...

# re: Scheduling automated backup using SQL server 2008

Wednesday, May 16, 2012 9:41 AM by OqOiLzAKvKF

arPP5Z Wow, great post. Keep writing.

# re: Scheduling automated backup using SQL server 2008

Friday, June 8, 2012 12:08 AM by Jyoti

Great help...thanks

# re: Scheduling automated backup using SQL server 2008

Friday, June 8, 2012 8:46 AM by Abdul Anoop

Absolutely perfect. Thank You so much for a wonderful demonstration..... Hats Off...

# re: Scheduling automated backup using SQL server 2008

Thursday, June 14, 2012 3:04 AM by Krishna

I was using the "History Cleanup Task".. instead of "Maintenance Cleanup Task". It was not working.

Your article help me.thanks a lot.

Krishna

# re: Scheduling automated backup using SQL server 2008

Sunday, June 17, 2012 12:08 PM by Ajay Rathi

Thanx a lot for the crystal clear tutorial. It worked for me.

# re: Scheduling automated backup using SQL server 2008

Monday, June 18, 2012 9:51 AM by Phani

Really useful. Very detailed step by step instructions.

Thankyou very much

# re: Scheduling automated backup using SQL server 2008

Thursday, June 21, 2012 8:05 AM by Breto

Excellent. Very detailed step by step instructions.

# re: Scheduling automated backup using SQL server 2008

Thursday, June 21, 2012 12:42 PM by Alexander Castiblanco

Muchas gracias por el aporte. voy a probarlo

# re: Scheduling automated backup using SQL server 2008

Friday, June 29, 2012 8:55 AM by rajasekarbabuk

thanks a lot sreejukg.it was really useful for me.

# re: Scheduling automated backup using SQL server 2008

Friday, June 29, 2012 8:56 AM by rajasekarbabuk

Thanks a lot boss.it was simply super

# re: Scheduling automated backup using SQL server 2008

Saturday, July 14, 2012 4:17 AM by maryam

how can i change name backup file in maintenance plan backup task?

# re: Scheduling automated backup using SQL server 2008

Wednesday, July 25, 2012 10:23 AM by Krishna

Thanks buddy..!!! It works for me...

# re: Scheduling automated backup using SQL server 2008

Sunday, July 29, 2012 2:56 PM by Gus

Very well explained, thanks a lot.

# re: Scheduling automated backup using SQL server 2008

Tuesday, July 31, 2012 6:20 AM by Thomas

Excellent work. Thanks

# re: Scheduling automated backup using SQL server 2008

Wednesday, August 1, 2012 2:37 PM by Ned Lyttelton

Huge help, thank you very much.