11

how can I schedule daily backup at 9:00pm in SQL server 2008?

nectar
  • 9,525
  • 36
  • 78
  • 100

5 Answers5

21

  1. Use Microsoft SQL Server Management Studio to create a new Maintenance Plan.

  2. In the object explorer go to the folder Management > Maintenance Plans.

  3. This will open the designer.

  4. In the Toolbox you will find a task named Back Up Database Task, drag this onto the designer.

  5. Right click 'Back Up Database Task' select edit from the context menu

  6. Backup type:

    • Full: Does a full backup each time (ref)
    • Differential: Makes a backup of the changes between last and current state. You will need all differentials + initial backup if you want to restore! (ref)
    • Transaction Log: Makes a backup of the transactions since last full backup. (ref)

  7. Right click the task to select the databases to backup.

Change the schedule of your maintenance plan to set when it will run.

Mantenance Plans

Ralf de Kleine
  • 11,464
  • 5
  • 45
  • 87
  • what are you talking about and what is folder management? – Neville Nazerane Mar 17 '14 at 05:14
  • Hi Neville, what's up? I'm talking about scheduling a backup for SQL Server 2008. I recon you can't get it to work? What't the problem? – Ralf de Kleine Mar 17 '14 at 08:45
  • No I mean I did not find any option "folder Management" in Microsoft SQL Server Management Studio. I however found another way to use schedule the backup using the scripts option in the backup dialogue. – Neville Nazerane Mar 17 '14 at 09:15
  • Edited the question and added an image. It could be the version of the Management Studio. Are you using Express by any chance? – Ralf de Kleine Mar 17 '14 at 10:28
  • I am sorry I had posted the comment before I found this option. Thanks a lot for the image. Also can I delete created plans/schedules from here? – Neville Nazerane Mar 17 '14 at 14:06
  • An explanation of the different backup types would only make this answer more valuable. – Zapnologica Jun 05 '14 at 06:33
  • 1
    @Zapnologica edited the answer and added information about backup-type – Ralf de Kleine Jun 05 '14 at 07:17
  • Differential backups are cumulative i.e. you only need the last one, not "all the differentials" as you state in your answer. Source: http://smehrozalam.wordpress.com/2009/08/18/ssms-how-to-restore-differential-backups/ – Will Warren Sep 19 '14 at 15:21
  • The pitfalls of not using proper punctuation! It should be "the folder, Management". – rgshenoy Dec 28 '14 at 18:25
  • @RohithShenoyG really? I'm not a native in English so forgive my ignorance. Can you elaborate? – Ralf de Kleine Jul 14 '17 at 09:26
11

Beside the solutions mentioned in this thread, I can recommend using ApexSQL Backup to create a SQL Server scheduled backup.

I’ve found an article that gives a brief introduction on backup types and recommended backup strategy practices (backup location, scheduled and automated backups, backup testing and verification).

It explains how to schedule backups in SQL Server by utilizing SQL Server Agent job, SQL Server Maintenance Plans and ApexSQL Backup: http://solutioncenter.apexsql.com/how-to-create-a-sql-server-scheduled-backup/

To create a SQL Server scheduled backup as a SQL Server Agent job, one needs to write a T-SQL script.

ApexSQL Backup can be used to avoid writing T-SQL scripts, but also to avoid the need to maintain backup plans and their corresponding jobs when using SQL Server Maintenance Plans.

The tool enables automating and scheduling SQL Server backup jobs in one task, while preserving the backup chain for easy point-in-time restoration (database rollback).

There is a wizard that guides you through the backup scheduling process. In the first step, the wizard asks for server name, database name and the backup type. Next – you’ll need to provide a destination folder for the backup and an output type. In the next step, you can choose from a variety of options such as backup verification, compression, encryption etc.

After that, in the next window, select the ‘Schedule’ option and specify occurring frequency, and finally – submit the scheduled backup and you’re done.

Phill C
  • 606
  • 7
  • 6
7

There's a nice script here http://ola.hallengren.com/ which when run creates some sql agents jobs that perform some of the maintenance tasks you should be doing on a database.

SPE109
  • 2,911
  • 1
  • 19
  • 16
  • 1
    Ola's solution is recommended in Pluralsight as a far superior solution to using the built-in database maintenance plans (Ola Hallengren's Maintenance Solution) http://pluralsight.com/training/Courses/TableOfContents/sqlserver-2012ic – Ricardo stands with Ukraine Sep 27 '13 at 14:28
1

Setup a Maintenance Plan using the Management tree within the SQL Server Studio:

Setting up a Maintenance Plan to Backup Databases

Turnkey
  • 9,266
  • 3
  • 27
  • 36
0

Create a maintenance plan. SQL 2008 has a great wizard for this in SSMS which will create a SQL Agent job that you can then inspect and modify - and see the SQL syntax for future reference.

Suggestion: create a backup device first.

pelazem
  • 1,352
  • 1
  • 11
  • 13