SQL Server 2008 and above error-operation Database recovery Method--Log tail backup (RPM)

Problem:

Often see someone mistakenly delete data, or misoperation, especially when update and delete did not add where, and then shouted father shouted Niang. Err who can have no, do wrong can understand, but can not indulge, this later, and now first to solve the problem.

In this case, usually do not do a backup, or will not come to ask. Be calm first, or there will be greater disaster. Until you give up.

Workaround:

For this type of problem, mainly to retrieve the data before the Misoperation, before 2008, there is a very famous tool log exploer, I heard that it is very useful, this online a lot of tutorials, here is not much to say. But the only regret is that the 2008 and later versions are not supported, and in addition to the other third-party tools, the most common is the method mentioned in this article-log tail backup. This experiment environment 2008r2, for 2008 and above version can use this method, actually 2005 also can, 2000 seldom use, haven't tried, just 2008 can use log exploer before, so there is no need to use this method.

The following illustrations explain the operation method, as to the principle, does not belong to the scope of this article, and I believe that when the wrong operation, it is estimated that no one will see the principle.

Steps:

(1), check the recovery model of the database,

Or use a script check:

1. SELECT Recovery_model,recovery_model_desc
2. From sys.databases
3. WHERE name =' AdventureWorks '

The results are as follows:

Ensure that the recovery model of the database is at least "simple". As for how to modify the whole mode, I think these should not be more necessary to say.

Remember that the full recovery model is strongly recommended for any important environment, not just the customer's formal environment (commonly known as the production environment), although for the other two (bulk-Logged (bulk_logged), simple), the full recovery model produces a large log But in the event of a problem, you will feel that this is nothing. And I can't imagine any reason not to use the full recovery model for a formal environment. The log of the full recovery model is not too perverted as long as it is properly managed.

(2), here is actually implied another step, has done at least once the full backup. Because all types of backups are based on a full backup, if there is not a minimum of one full backup, other types of backups are redundant, so emphasize here that after creating a new database, it is strongly recommended that you even force a full backup.

1. SELECT Database_name,recovery_model,name
2. From Msdb.dbo.backupset

Using the above statement roughly can see that there are those databases have been backed up, because of the test, so do a few backups, you can see that I have done a backup at this point in time.

(3), make sure others no longer connect to the database, and then do a log tail backup:

First create a bit of data:

2. /*
4. Because tempdb is always a simple recovery model, it is not suitable for cases.
6. Here we use Microsoft's sample database AdventureWorks
8. */
10. Use AdventureWorks
12. GO
14. IF object_id (' Testrestore ') is not NULL
16. DROP TABLE testrestore
18. GO
20. CREATE TABLE Testrestore
22. (
24. ID INT IDENTITY (1, 1),
26. NAME VARCHAR
28. );
30. --Insert test data:
32. INSERT into testrestore (Name)
34. SELECT ' test1 '
36. UNION All
38. SELECT ' test2 '
40. UNION All
42. SELECT ' test3 '
44. UNION All
46. SELECT ' test4 '
48. UNION All
50. SELECT ' Test5 '
52. UNION All
54. SELECT ' Test6 '
56. UNION All
58. SELECT ' test7 '
60. UNION All
62. SELECT ' Test8 '
64. SELECT * from testrestore

Check the results:

Then to do a delete operation, in order to locate when it happened, I added a waitfor command, let it happen at some time, so that when the recovery is accurate:

1. Use AdventureWorks
2. GO
3. WAITFOR time ' 21:45 '
4. DELETE from Dbo.testrestore

Now let's look at the data:

1. Use AdventureWorks
2. GO
3. SELECT * from dbo.testrestore

By this step, the disaster has come. But remember to be calm.

Here is the focus of this article to start, do a log backup, the most important is to select "Backup Log Tail"

Then on the Options page, select: Except for the transaction log, where the other red box parcels are strongly recommended. and ensure that the database does not have someone else on the connection, because the tail of the backup log causes the database to be in a restored state, deny connections to other sessions, and cannot be backed up without disconnecting other connections.

Then press OK, and of course, you can use the "script" above to generate the statement:

2. Use Master
4. GO
6. BACKUP LOG [AdventureWorks] to DISK = N' E:\AdventureWorks.bak ' with No_truncate, Noformat, Noinit, NAM E = N' adventureworks-transaction log backup ', SKIP, Norewind, Nounload, NORECOVERY, COMPRESSION, STATS = ten, CHECKSUM /c2>
8. GO
10. Declare @backupSetId as int
12. Select @backupSetId = Position from msdb. Backupset where database_name=n' AdventureWorks ' and backup_set_id= (select max (backup_set_id) From msdb: Backupset where database_name=n' AdventureWorks ')
14. If @backupSetId is a null begin RAISERROR (N' validation failed. The backup information for database "AdventureWorks" could not be found. ', 1) end
16. RESTORE verifyonly from DISK = N' E:\AdventureWorks.bak ' with FILE = @backupSetId, nounload, Norewind
18. GO

At this point, the database is in the "Restoring" state

If you do not see the backup can be viewed with the following statement, and the SPID killed:

1. SELECT * from sys.sysprocesses WHERE dbid=db_id (' AdventureWorks ')

Execution Result:

and kill.

Then continue with the backup.

And then restore,

To restore the full backup, select the most recent, because of the characteristics of the log backup (other articles later), only the last backup, so choose the latest one, or you can not restore.

Here is another note, remember to choose:

Then restore the log file, which is one of the most important steps:

And then:

Due to some problems in the experiment, after the redo, so the time to choose 22:19 minutes, I was in the 22:20 minutes to delete data. Don't worry too much here, just assign the time point to the time you accidentally deleted. And since the tail of the log is the last backup file, the Red-box section can be selected here:

Now check again:

As you can see, the data has been restored successfully.

Summarize:

Usually do not do backup, the problem to shout urgent, this is Gou have to pick up, there are some people like to see the big head of LDF directly deleted, after the problem will not blame Microsoft.

The method in this article looks a bit cumbersome, but actually a few times feel good, but the steps are recommended strictly according to the above, because once the operation is wrong, it is troublesome, at this time again stressed-calm calm and calm down!!!!!!

There are several drawbacks to this approach:

1, if you find that the wrong operation after a lot of people do the operation, then you restore success, the operation of others will be washed away, so after the wrong operation, to immediately stop others on the database operation.

2, this method to the database exclusive, so you want to secretly restore is not possible. Admit your mistakes bravely.

For the core data table, or to do the prevention operation first, you can see: SQL Server to recover table-level data.

SQL Server 2008 and above error-operation Database recovery Method--Log tail backup (RPM)