How to Setup Automatic Daily Database Backups in SQL Server 2008

Schedule automatic database backups in SQL Server with Management - Maintenance Plans.
January 13, 2010 By
Use the Maintenance Plan Wizard in SQL Server Management Studio to configure and setup automatic daily backups for your databases.

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.