SQL Server Data Recovery to pointing point in time (Full recovery)

SQL Server Data Recovery to pointing point in time (Full recovery)

When it comes to database recovery, in fact, we generally have two of the most common, one is simple recovery, through the backup bak file directly to restore the database, the disadvantage is that there may be data loss. Another is to restore data by backing up the data + transaction log. However, we need to ensure the integrity of the transaction log if the latter is restored. What we're going to focus on today is the second, all the operations we're using in SSMs.

Note: The second method to restore the data need to be aware of the problem: 1, to prepare the full backup of the data before, 2, back up the new transaction log. 3. Complete restore with full backup data. 4. Restore data at a specified point in time by a new transaction log backed up.

We first create a test database---DB2, and then create a table info

Defining field Information

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYPfx3UlAAAFOF1wXcTw830.png" height= "/>"

We insert data

SELECT * from Infoinsert into info (idcode,age) VALUES (' Zs ', +) insert into info (idcode,age) VALUES (' ls ', +) insert INTO info (idcode,age) VALUES (' Gwl ', 27)

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYPixcRfMAAHceio_Daw457.png" height= "422"/>

Next we back up the data, back up the road D:\DB_BACKUP

Right-click the database---task--backup

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYPujPXpNAAICyJc33OY564.png" height= "484"/>

Backup type: Complete.

Backup Road Strength: D:\db_backup\db2.bak

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYP2BRPCRAAHkQEx3skg387.png" height= "419"/>

Backup complete

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQDzFn9nAAH3xCeeBUA120.png" height= "413"/>

Backup completed files

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYQPSDeyuAACDVEIybPA261.png" height= "312"/>

After a successful backup, we add a few more data and then delete a few data (we need to be aware of the point in time to facilitate later operations)

At this time is the result of our mistake operation, after the operation, we can restore the normal.

It is important to note that the input writes and deletes are likely to be performed by different users, so it is necessary that the data is lost by means of a restore, so we also need to restore through log when we have completed the full backup recovery.

Current Time 21:32

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://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYQTihJ1oAADqKX8cwa0080.png "height=" "/>

We've inserted a few data

SELECT * from Infoinsert to info (idcode,age) VALUES (' XLL ', 118) insert into info (idcode,age) VALUES (' WC ', ") insert INTO info (idcode,age) VALUES (' LC ', 127)

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQXDIWP8AAIDNc2yB4Y711.png" height= "456"/>

We have a few minutes to delete data--delete 22 test data

The current time is:

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYQbiLUp8AAEwF6MKj-k906.png" height= "298"/>

Next we delete two data

SELECT * from Infodelete info where idcode = ' ls ' Delete info where idcode = ' GWL '

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYQejZ0UJAAIXe1yIwZc055.png" height= "478"/>

At this point we find that the data is abnormal so we need to restore it through backup. However, after our backup, we did some data insertion, if the recovery of the previous backup, the data must have been lost, so we also need to use the log to restore.

So we need to restore it through a full backup, then recover it through log, then restore to a specified point in time.

Before recovering a full backup, we need to make a fresh transaction log backup.

In fact, the backup transaction log directory is to write truncation, to ensure the integrity of the data

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYQizQBIAAAEo0Miro8I366.png" height= "287"/>

Database---Right-click--Task---backup---backup type--transaction log

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQnxVISwAAD7Daiad9o233.png" height= "484"/>

After the backup is successful

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQmhXDduAACFE2TEghM121.png" height= "259"/>

At this point we need to restore the following data through a full backup

Database---Task---restore--database

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQuiAdSJAAG_xXnj8bc809.png" height= "484"/>

Select a device---Browse backed up database files

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQ6wXedJAAHn07CVx4c879.png" height= "470"/>

Remember, we must select Norecovery in the options
Restore status: Restore with NORECOVERY

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRHxUOVDAAIsdDDgJ3g661.png" height= "464"/>

Start the restore prompt for the following information:

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRTQdoUwAAJbOVsLqtc732.png" height= "464"/>

We can also use the following script to restore

Use [Master]backup LOG [DB2] to DISK = N ' D:\Microsoft SQL server\mssql11. Mssqlserver\mssql\backup\db2_logbackup_2017-03-19_22-40-40.bak ' with Noformat, noinit, NAME = N ' DB2_LogBackup_ 2017-03-19_22-40-40 ', Noskip, Norewind, Nounload, NORECOVERY, STATS = 5RESTORE DATABASE [DB2] from DISK = N ' D:\DB_BACKUP\ Db2.bak ' with FILE = 1, NORECOVERY, nounload, STATS = 5GO

There are two solutions to these problems

1. When restoring the database, click on the option on the Select page, tick overwrite existing database (with REPLACE), click OK to restore the database successfully (recommend this method).

650) this.width=650; "title=" 0-365228085 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "0-365228085" src= "http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYRXxzxhtAAB83rWHCrQ064.jpg" height= "201"/>

2, when the restore operation, click the option on the Select page, tick keep the source database in the restoring state (BACKUP LOG with NORECOVERY), can solve the problem.

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYRjhot_HAAI3Af8MVLE043.png" height= "465"/>

We choose the second method to restore the success of the normal

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYRvTo-lZAAG8AsLNEz4071.png" height= "463"/>

At this point we look at the database has been prompted again---is being restored, the phenomenon is normal.

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRyx22BrAAERV3efb-0263.png" height= "459"/>

Next we will restore the data through the transaction log.

Database---Task---restore---transaction log

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYR6yyfzbAAGs2hRlJFs866.png" height= "484"/>

Browse to the saved transaction log file-db2.trn

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYSCRmJKqAAGJDlmXzwM198.png" height= "471"/>

Then we need to choose the date and time to delete the data: We have recorded the date and time of deletion of data 21:36

This is the key place to recover data.

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYSKS5r4jAAGBl1sBjus494.png" height= "421"/>

Confirmed point-in-time information

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://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYSPy7hOSAAGBdHo-H78410.png "height=" 415 "/>

We need to click the Options menu at this point:

Recovery status: Rollback of uncommitted transactions, use of the database to process the state that can be used. Unable to restore other transaction logs (Restore with RECOVERY)

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYSTi0WY5AADYxNNsBog495.png "height=" 367 "/>

We can also use a script to restore data.

RESTORE LOG [DB2] from DISK = N ' D:\DB_BACKUP\DB.trn ' with FILE = 1, nounload, STATS = ten, STOPAT = N ' 2017-03-19t21:36:01 ' G O

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYSXy23mPAAGLiKj7_i8084.png" height= "418"/>

Database status is displayed correctly after restore success

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;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYSaxAHfIAAF8PWlTI4g134.png" height= "457"/>

Next we look at the data and return to normal;

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://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYSiRHGbJAAFpZaJSKYk223.png "height=" 438 "/>

This article from "Gao Wenrong" blog, declined reprint!

SQL Server Data Recovery to pointing point in time (Full recovery)