how to schedule backup in sql server 2008? [closed

问题:

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

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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

Setting up a Maintenance Plan to Backup Databases

回答5:

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.