How to Setup Automatic Daily Database Backups in SQL Server 2008

Schedule automatic database backups in SQL Server with Management - Maintenance Plans.
By

Are you backing up your SQL Server 2008 databases daily?  You should be, especially if you don't want to lose any of your precious data that you're storing.  It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or more databases. To get started backing up your databases using SQL Server Management Studio follow the steps below.

Here's how to setup automatic daily backups for SQL Server 2008 databases:

  1. Open Microsoft SQL Server Management Studio.
  2. Expand the database server.
  3. Expand the Management folder.
  4. Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.
  5. Click 'Next' button, then name your Maintenance Plan and give description.  Select the radio button that says "Single schedule for the entire plan or no schedule".
  6. Under Schedule,  click on the 'Change' button.  This brings up the Job Schedule Properties form. In the Frequency section, change Occurs to 'Daily', and then click 'OK.
  7. Click 'Next' button, then check the box next to "Back Up Database (Full)", then click 'Next'.
  8. Click 'Next' button again, then select one or more of your Databases to be backed up using the Database(s) drop down box. Make any additional settings and then click 'Next'.
  9. On the Select Report Options form, click the 'Next' button once again.
  10. Then click the 'Finish' button to complete the wizard.
  11. The Maintenance Plan Wizard will run and should complete successfully.  Click the 'Close' button.
  12. You should now see the database backup maintenance plan you just created underneath the Maintenance Plans folder in SQL Server Management Studio.

UPDATE 6-20-2012: IF you need to automatically back up SQL Server Express databases (or even MySQL, SQL Server), I'd highly recommend you check out SQLBackupAndFTP  (or MySQLBackupAndFTP).  It's such an easy way to backup your SQL Server Express databases automatically, without having to write your own custom script.  It can also backup to Dropbox, a server Folder, FTP, Amazon S3, and will soon backup to Google Drive and SkyDrive.  They even offer a free version. Download it here:  SQLBackupAndFTP and MySQLBackupAndFTP 


0
21

21 Comments

anonymous by Chris on 3/3/2010
Is this option available for all versions? I follow along and then when I get to the last step, all of the buttons are greyed out, except for the ones to cancel and/or go back. Thanks.
Doug by Doug on 3/3/2010
Chris, unfortunately the "Maintanence Plans" are not available in SQL Server 2008 Express edition, so if you're using Express edition you'll probably have to write a custom backup job script using Windows Task Scheduler to perform automatic backups.
anonymous by Jayadev Vayalathara on 11/20/2010
Thanks for you support ...! It is working with SQL Server 2008 Std....!
Thank You so much...!
With Regards
Jayadev
anonymous by Jay-R on 2/9/2011
Thanks. It helped me for sql server 2008 r2 standard..
anonymous by K on 2/28/2011
Thanks.

notes to myself, remember to:
-Login as admin in SQL to set this up
-Have integration services installed.
-Check windows services that SQL Server Agent that it is set to Automatic or Automatic startup type (else when servers auto restart your backup routine is broken)
anonymous by Ehab on 4/19/2011
Thanks you give the key to know
anonymous by Muthukumar.N on 5/23/2011
Thanks... Its working fine in 2008 R2
anonymous by Mohamed Abd El-Sater on 6/14/2011
Thanks a lot
anonymous by Toti on 8/25/2011
Hey, how come that it is existing in the maintenance and job but it was never triggered? I'm not using an express edition. Thanks
anonymous by Mark on 12/2/2011
This does not work. 100%.. it creates a separate backup for each database with thedate and time appended. I require it to overwrite dailay as in SQL2005 :
BACKUP DATABASE [DB] TO DISK = N'C:\SQL-Backup\dbMON.bak' WITH NOFORMAT, INIT, NAME = N'DB', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
anonymous by anITa on 12/10/2011
I am nt getting any 'Maintenance Plans' in SQL 2008
anonymous by anita on 12/10/2011
is there any other solution to take automatically backup?? because 'Maintenance Plans' not available in my sql 2008
anonymous by Ken on 2/7/2012
This is great! It works for me
anonymous by Dilip Mevada on 5/4/2012
Thanks for sharing nice options.

I have one question to schedule backup at remote machine / location means other than our local machine.

Please advice me on this.
anonymous by Kishore Varanasi on 10/3/2012
Thank you very much, it is working well.
anonymous by lazymind on 11/14/2012
can i append it.. i dont want to write the database everytime. i want to create a new database backup what should i do for the same?? it works fine for over writing the existing backup.
anonymous by Ejaz on 11/21/2012
Doug, Thanks a lot, a great Job!
anonymous by kurei on 12/5/2012
it works for me... thanks
anonymous by Ara on 2/4/2013
Thanks, Its working fine
anonymous by Ivan Reynoso on 9/4/2013
Thanks for ur help... It worked really fine for me...
anonymous by heyman on 11/6/2013
thanks for the help it worked 100

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.


Related Posts


When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database. Open SQL Server Management Studio In the Object Explorer sidebar, right-click on the top SQL...  more »

If you’ve experienced as many problems as I have while trying to Import/Export my Sql Server 2005 database to a hosting provider for the first time, then hopefully this how-to will be of some assistance to you.  more »

I would like to learn how to get the skills and education to be able to build interactive websites. Can you give me some help and explain how to get started?  more »

UPDATE 12-16-2011: If you are using ASP.NET and your website / database is live, and you want to take the database offline, you're better off putting an App_Offline.htm file in your website main directory to first take the website down for maintance (I...  more »

Today I ran into the a "Restore failed for Server" error message while trying to restore a SQL Server 2008 database using a database backup (.bak) file. Below I will show you how to fix this restore error, so that you can restore your SQL Server database...  more »

I recently installed SQL Server 2008 database on my local computer and forgot to enable the common language runtime (CLR). Because I had forgotten to turn it on, some portions of my sites were not fuctioning properly due SQL Server not being able to...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »