Set up MS SQL Server 2008 periodic automatic backups

1. Description

SQL Server2008 itself has regular automatic backup capabilities, and we only need simple configuration to enable very simple and efficient automatic backup.

2. Open the SQL Server Agent service

To implement the automatic backup feature, first make sure that SQL Server's SQL Server (proxy) service is turned on.

If you do not see this SQL Server Agent node, you can open it in the following way

Find "Microsoft SQL Server R2" in the Start menu and select "SQL Server Configuration Manager" in "Configuration Tools"

Select SQL Server service in the SQL Server Configuration Manager window, and then right-click SQL Server Agent to select Start

3. Set up a backup plan

Suppose you now have a database called "test" and now you want to make regular backups of this database

Right-click Maintenance plan under the Administration node and select the Maintenance Plan Wizard, as

If the following error window prompt appears

Resolve as follows

"Start" = "Run", open the console window,

If it is a 32-bit operating system, run the following command

C:\windows\system32\regsvr32 "C:\Program Files\Microsoft SQL Server\100\dts\binn\dts.dll"

If it is a 64-bit operating system, run the following command

C:\windows\syswow64\regsvr32 "C:\Program Files (x86) \microsoft SQL Server\100\dts\binn\dts.dll"

Then turn Microsoft SQL Server Management Studio off before you open it.

If there is no error, the Maintenance Plan wizard window appears

Click "Next" button

Enter a name for the maintenance plan, and then click "Change ..." to set the time to back up the database, as

After the execution time is set, click "OK", go back to the original window, then click "Next",

In the new window, tick "Back Up Database (full)" and click "Next"

This window is the order in which tasks are executed, because we only set a task, we don't have to do this, just "next"

Select the database we want to back up "test"

It is best to check "create subdirectories for each database" and "Verify backup integrity", expire time and compression, and then click "Next"

This step is mainly how to handle the backup log, we can put in and back up the same directory or use the default directory, if placed in the backup file directory, view will be convenient, click "Next"

You can complete the automatic backup setup by completing the last point.

Finally, our Maintenance Plan Settings window appears, and the backup schedule is not yet executed.

In the Maintenance Plan node under administration and the Jobs node under SQL Server Agent, you can see the backup schedule that we have set up.

4. Execute backup plan now

We can also implement the backup plan we set up without waiting for the preset time, as follows

In the maintenance plan, locate the backup plan that we just established, called "Automatic backup Database", right-click on "Execute"

After successful execution, the original backup directory D:\Program Files\Microsoft SQL Server\mssql10_50.mssql\mssql\backup See the folder named after the backup data, open the folder can see the backup file

Depending on the settings, the backup of each database is placed in a folder named after the database name.

A backup file that is generated after the backup plan is executed.

If the backup process reports logs using the default configuration, the backup log can be seen in the log folder of the database, for example, each time a backup produces a log of a text file.

You can see the backup log when you open it

If the execution fails with the following error message, the SQL Server Agent service is not turned on.

5. Restore Backup

Right-click on the database you want to restore (this example is "test"), task = "restore =" Database ...

Select a restore point based on time

Backup is a time as a restore point, by selecting a different "target point in time" can be restored to a different backup, if we modify the "target point in Time", then the lower "Select to restore the backup set" is checked in the name will be changed accordingly. is a dialog box that changes the target point in time.

Click "Options", tick "overwrite existing database" and "Restrict access to restored database"

Point "OK"

6. Modify the Backup Schedule

Under "Maintenance Plan", right-click on our new "Automatic Backup Database" and select "Modify", as

In the new tab that opens, you can modify the backup behavior, the time of the backup, the backup log storage location, and so on.

Set up MS SQL Server 2008 periodic automatic backups