How to Setup Automatic Daily Database Backups in SQL Server 2008

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

by Doug Updated February 15, 2013

Use the Maintenance Plan Wizard in SQL Server Management Studio to configure and setup automatic daily backups for your databases.

enlarge

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:

Open Microsoft SQL Server Management Studio.
Expand the database server.
Expand the Management folder.
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.
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".
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.
Click 'Next' button, then check the box next to "Back Up Database (Full)", then click 'Next'.
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'.
On the Select Report Options form, click the 'Next' button once again.
Then click the 'Finish' button to complete the wizard.
The Maintenance Plan Wizard will run and should complete successfully. Click the 'Close' button.
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

22 Comments

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.
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.
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
by Jay-R on 2/9/2011 Thanks. It helped me for sql server 2008 r2 standard..
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)
by Ehab on 4/19/2011 Thanks you give the key to know
by Muthukumar.N on 5/23/2011 Thanks... Its working fine in 2008 R2
by Mohamed Abd El-Sater on 6/14/2011 Thanks a lot
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
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
by anITa on 12/10/2011 I am nt getting any 'Maintenance Plans' in SQL 2008
by anita on 12/10/2011 is there any other solution to take automatically backup?? because 'Maintenance Plans' not available in my sql 2008
by Ken on 2/7/2012 This is great! It works for me
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.
by Kishore Varanasi on 10/3/2012 Thank you very much, it is working well.
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.
by Ejaz on 11/21/2012 Doug, Thanks a lot, a great Job!
by kurei on 12/5/2012 it works for me... thanks
by Ara on 2/4/2013 Thanks, Its working fine
by Ivan Reynoso on 9/4/2013 Thanks for ur help... It worked really fine for me...
by heyman on 11/6/2013 thanks for the help it worked 100
by Tawny-Eagle on 2/27/2019 Clearly written instructions and it worked first time for me using SQL 2008 R2. Thanks so much for taking the time to share these instructions.

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.

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.

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

by Jay-R on 2/9/2011

Thanks. It helped me for sql server 2008 r2 standard..

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)

by Ehab on 4/19/2011

Thanks you give the key to know

by Muthukumar.N on 5/23/2011

Thanks... Its working fine in 2008 R2

by Mohamed Abd El-Sater on 6/14/2011

Thanks a lot

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

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

by anITa on 12/10/2011

I am nt getting any 'Maintenance Plans' in SQL 2008

by anita on 12/10/2011

is there any other solution to take automatically backup?? because 'Maintenance Plans' not available in my sql 2008

by Ken on 2/7/2012

This is great! It works for me

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.

by Kishore Varanasi on 10/3/2012

Thank you very much, it is working well.

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.

by Ejaz on 11/21/2012

Doug, Thanks a lot, a great Job!

by kurei on 12/5/2012

it works for me... thanks

by Ara on 2/4/2013

Thanks, Its working fine

by Ivan Reynoso on 9/4/2013

Thanks for ur help... It worked really fine for me...

by heyman on 11/6/2013

thanks for the help it worked 100

by Tawny-Eagle on 2/27/2019

Clearly written instructions and it worked first time for me using SQL 2008 R2.
Thanks so much for taking the time to share these instructions.

Add your comment

by Anonymous - Already have an account? Login now!

Your Name: Your Name is required.

Comment: Comment is required.

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.

How to Setup Automatic Daily Database Backups in SQL Server 2008

22 Comments

Add your comment

Related Posts

Ask a Question

Article Categories

Follow Us