SQL Server 2012 notes Sharing-24: Backup and Recovery Scenario 2

Scene

How do I revert to the point of failure after the last backup?

Backup Scenarios

with full + diff + transaction logs interspersed with each other in a way

Restore Scenarios

Use the full restore Mode

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

First, we create a full backup for DB01.

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/17/639838_1402976015XCXM.png "height=" 484 "/>

We then renamed Test-table to Table-test and then performed a differential 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/17/639838_14029760169xPG.png "height=" 271 "/>

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/17/639838_1402976016Cfck.png "height=" 484 "/>

After completing the above full and differential backups, we renamed the Table-test table to table-test01.

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/17/639838_1402976016rt9p.png "height=" 233 "/>

Then this time we have not done any backup for table-test01, we simulate the database failure is not available at this time, first stop the SQL service, and then delete the DB01 database file.

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/17/639838_1402976017n9w2.png "height=" 224 "/>

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/17/639838_1402976018FNi6.png "height=" 312 "/>

After simulating the failure, we found that the database was in a resume-pending state.

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/17/639838_1402976019QNqO.png "height=" 326 "/>

Let's deal with the failure and restore the database to a state of failure.

First select the server object--backup device--Back up the database.

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/17/639838_1402976020weco.png "height=" 484 "/>

Select the database that you want to back up as DB01, which is our failed database, and then select the backup type for the transaction log backup.

650) this.width=650; "title=" border= "0" alt= "src=" http://img1.51cto.com/attachment/201406/17/639838_ 14029760215hzm.png "height=" 484 "/>

Switch to the Options page, tick "Back up the tail of the log and keep the database in a restored state".

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/17/639838_1402976024ba4z.png "height=" 484 "/>

After the backup is complete, the.

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/17/639838_1402976025eea5.png "height=" 484 "/>

At this point, we find that the state of the database changes to the state being restored.

650) this.width=650; "title=" PNs "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=" PNS "src="/http Img1.51cto.com/attachment/201406/17/639838_14029760259bbu.png "height="/>

Then we right-click on the database, select the task-restore-database,.

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/17/639838_1402976026gvwn.png "height=" 335 "/>

Also tick the full and differential backup of the database before the failure, and then tick the tail transaction log backup that we just created.

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/17/639838_1402976027eacx.png "height=" 484 "/>

After the restore is complete.

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/17/639838_1402976027kkad.png "height=" 484 "/>

Now we see that the database has returned to its pre-failure state, that is, we modified the table to table-test01 state.

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/17/639838_1402976028hvom.png "height="/>

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

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/17/639838_1402976028IuXn.png "height=" 205 "/>

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/17/639838_1402976029FXKg.png "height=" 221 "/>

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