SQL Server Data Recovery--log backup

It's too much, I've erased the data.

"Big hole, the data was accidentally deleted by me." "In the process of database-related work, I think many people will have the same experience as I do?" In particular, when an update or delete operation is performed, the Where condition is forgotten. These destructive actions are enough to make you not calm. Then began to Baidu, CSDN, Csblogs and so on to find a recovery program. On the Internet, there are also divergent opinions, for a time, you collapsed again. This time to learn to calm, otherwise, will break your heart.

Fortunately, data recovery is also possible

After this kind of problem, our aim is to retrieve the data before the Misoperation, before the SSMS2008, there is a very famous tool logexploer, but it seems to be charged (anyway, I am the instrument of charge, is to avoid and far away). In addition to this, we can use the function of log backup to complete data recovery.

Let's explain how to use log backup to recover data (there is a picture of the truth).

Preparatory work:

1. Run the following code to create the Recoverdemo database, and the TestTable table.

1  Use [Recoverdemo]2 GO3 /** * * * object:table [dbo].    [TestTable] Script date:10/22/2014 17:00:36 * * * * **/4 SETAnsi_nulls on5 GO6 SETQuoted_identifier on7 GO8 SETAnsi_padding on9 GOTen CREATE TABLE [dbo].[testtable]( One     [A] [varchar]( -)NULL A) on [PRIMARY] - GO - SETAnsi_paddingOFF the GO - INSERT [dbo].[testtable]([A])VALUES(N'A1') - INSERT [dbo].[testtable]([A])VALUES(N'A2') - INSERT [dbo].[testtable]([A])VALUES(N'A3') + INSERT [dbo].[testtable]([A])VALUES(N'A4') - INSERT [dbo].[testtable]([A])VALUES(N'A5') + INSERT [dbo].[testtable]([A])VALUES(N'A6') A INSERT [dbo].[testtable]([A])VALUES(N'A7') at INSERT [dbo].[testtable]([A])VALUES(N'A8') - INSERT [dbo].[testtable]([A])VALUES(N'A9') - INSERT [dbo].[testtable]([A])VALUES(N'A10')

2. Then do a full backup, as for the steps there is no need to say it. (The prerequisite for recovering data is that the database has done at least one full backup)

Tip: Because all types of backups are based on a full backup, if you do not have at least one full backup, the other backups are redundant, so be sure to do a full backup after you have completed the database.

3. Confirm that the recovery model for the database is complete.

Tip: In a production environment, it is highly recommended to use the full recovery model, even if the full recovery model produces a large log relative to the other two recovery models, but this is nothing when there is a problem.

4. Execute the following script to add data to the testtable.

1 INSERT [dbo].[testtable]([A])VALUES(N'A11')2 INSERT [dbo].[testtable]([A])VALUES(N'A12')3 INSERT [dbo].[testtable]([A])VALUES(N'A13')4 INSERT [dbo].[testtable]([A])VALUES(N'A14')5 INSERT [dbo].[testtable]([A])VALUES(N'A15')6 INSERT [dbo].[testtable]([A])VALUES(N'A16')7 INSERT [dbo].[testtable]([A])VALUES(N'A17')8 INSERT [dbo].[testtable]([A])VALUES(N'A18')9 INSERT [dbo].[testtable]([A])VALUES(N'A19')Ten INSERT [dbo].[testtable]([A])VALUES(N'A20')

Check Result: SELECT * from TestTable

The disaster happened.

1. We have mistakenly manipulated the deletion (delete from testtable) to the data in TestTable.

This time, you should not calm down.

We're here to recover the data.

The previous ones are a point, and the next is what we want to talk about.

1. Do a log backup, remember to select "Back up the tail of the log."

Then in the "Options", strictly follow the selection in the Red box to check, otherwise, you may cry. and ensure that the database does not have a connection, because there is a link, log backup is not backup.

2. After the log backup, the database is in the "Restoring state ..."

The data can be restored after the log backup

3. To restore a full backup, be sure to select the last full backup, otherwise it will not be restored.

4. The final step is to restore the log backup, choose the point of time to restore not too much attention, as long as you fill in the wrong time before the point of operation, you can.

Haha, the data restore was successful.

Future

1. Be cautious when carrying out hazardous operations, so as not to cause unnecessary trouble.

2. Be sure to develop the habit of backup, do not because the log file is too large, the recovery mode is set to "simple", or even delete log files, to that time the immortal may not be you.

SQL Server Data Recovery--log backup