SQL Server 2012 notes Sharing-23: Backup and Recovery Scenario 1

Scene

An enterprise server has a database DB01, the database can only be updated every Sunday 0:00 data, and the amount of data is not large, the development of that kind of backup plan more appropriate? How do I set it? What is the method of restoring?

Backup Scenarios

Take a full backup once every Sunday.

Restore Scenarios

The requirements are met in a simple mode, as the DB01 is only updated in Sunday.

Scenario Analysis

The Data Loss Time window under the simple recovery model, such as.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885308CJsC.png "height=" 233 "/>

In simple mode, you can restore a full backup of the database by restoring the most recent full backup.

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [1] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885308FOaW.png "height=" 279 "/>

Configuration method

The first type: graphical interface

First create a DB01 database:

650) this.width=650; "title=" clipboard[2] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [2] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885309gOuY.png "height=" 484 "/>

We then create a table in the database that simulates the update of the data at 0:00 in the evening.

650) this.width=650; "title=" clipboard[3] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [3] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885310H4ae.png "height=" 484 "/>

The recovery model for the configuration database is simple.

650) this.width=650; "title=" clipboard[4] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [4] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885310O249.png "height=" 484 "/>

Next we perform a full backup of the database.

650) this.width=650; "title=" clipboard[5] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [5] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885312YIh1.png "height=" 484 "/>

The backup type is a full backup.

650) this.width=650; "title=" clipboard[6] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [6] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885312Y4Vx.png "height=" 484 "/>

In the options interface, we can configure the reliability of the backup.

650) this.width=650; "title=" clipboard[7] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [7] "src=" Http://img1.51cto.com/attachment/201406/16/639838_14028853134Raa.png "height=" 484 "/>

You can also choose whether to compress the data at the time of backup.

650) this.width=650; "title=" clipboard[8] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [8] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885317Pal8.png "height=" 484 "/>

After the backup succeeds, the.

650) this.width=650; "title=" clipboard[9] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [9] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885317b1bZ.png "height=" 484 "/>

=============================================================================================================== =====

By default, the backup data is stored in the default location of the C drive, the file ends in. bak, and we can also create a backup device to store the data on the backup device, following the process of a custom backup device.

In the select new backup device,.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/16/639838_1402885318bzqs.png "height=" 484 "/>

Enter the device name and enter the destination file location for the backup device, which is the Fullbackup file under the Full-backup folder under the C drive.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/16/639838_1402885322ki1i.png "height=" 484 "/>

After the creation is complete, as shown in.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/16/639838_1402885324i5jc.png "height=" 193 "/>

Then, when we do a data backup, we can choose to save the backup on the backup device.

650) this.width=650, "title=", "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/16/639838_1402885325aef0.png "height=" 484 "/>

=============================================================================================================== ======

Let's take a look at how to use a backup restore if the data table we created is missing.

First I manually delete the previously created testtable.

650) this.width=650; "title=" clipboard[10] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [Ten] "src=" http://img1.51cto.com/attachment/201406/16/639838_1402885328AHZN.png "height=" 463 "/>

Select Restore database as shown in.

650) this.width=650; "title=" clipboard[11] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [One] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885328Gwau.png "height=" 335 "/>

Select the full backup device.

650) this.width=650; "title=" clipboard[12] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [src=] "http://img1.51cto.com/attachment/201406/16/639838_1402885329b9WN.png" height= "484"/>

Select the backup set that you want to restore.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/16/639838_1402885329wect.png "height=" 484 "/>

Then switch to the Options screen, tick overwrite the existing database, and restore the status to restore with recover.

Note: If you need to restore differential and log backups after you have finished restoring a full backup, you can select the second restore with no recovery.

650) this.width=650; "title=" clipboard[13] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885330VxEf.png "height=" 484 "/>

After the restore is complete.

650) this.width=650; "title=" clipboard[14] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402885330wRlC.png "height=" 484 "/>

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!