SQL Server 2008 and later database recovery methods log Tail backup _mssql2008

Often see someone mistakenly deleted data, or misoperation, especially update and delete when there is no add where, and then shouted Dad 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 backup, or will not come to ask questions. Be calm first, or there will be greater disaster. Until you give up.

Workaround:

For this kind of problem, the main is 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 will not say more. However, the only regret is that 2008 and later versions are not supported, and the most common use of this article, except for other third-party tools, is the log-tail backup. This article experimental environment 2008r2, for 2008 and above version can use this method, in fact 2005 also can, 2000 rarely use, did not try, just 2008 can use log exploer, so there is no need to use this method.

The following illustrated the operation method, as to the principle, does not belong to the scope of this article, and I believe that when you really encounter misoperation, it is estimated that no one will see the principle.

Steps:

(1), check the recovery model of the database, as shown:

Or use a script check:

SELECT Recovery_model,recovery_model_desc from 
sys.databases 
WHERE name = '

The results are as follows:

Ensure that the recovery model for the database is at least "simple". As for how to change into a complete model, I think there should be no need to say more.

remember that for any important environment, not just the customer's formal environment (commonly known as the production environment), it is highly recommended that the full recovery model be used, although for the other two (bulk-Logged (bulk_logged), simple), the full recovery model generates a large log size, But when there is a problem, it feels like it's nothing. And I can't imagine any reason for not using the full recovery model for the formal environment. As long as it is properly managed, the full recovery model log is not too abnormal.

(2), here actually implies another step, has done at least once a full backup. Because all types of backups are based on full backups, other types of backups are redundant without having at least one full backup, so here's a strong recommendation that you even force a full backup after you create a new database.

SELECT Database_name,recovery_model,name from 
ms

Use the above statement rough can see those databases do backup, due to the test, so several backups, you can see that I have done a backup at this point in time.

(3), make sure that someone no longer connects to the database, and then do a log tail backup:

First, create a little bit of data:

Because tempdb is always a simple recovery model, it is not suitable for cases.
Here we use the Microsoft sample database AdventureWorks

* 
/Use AdventureWorks go 
IF object_id (' Testrestore ') are not NULL 
 DROP TABLE 
testrestore 
Go CREATE TABLE testrestore 
 ( 
  ID INT IDENTITY (1, 1), 
  NAME VARCHAR 
 ); 
--Insert test data: INSERT INTO  
testrestore (Name) 
select ' Test1 ' 
union ALL 
Select ' Test2 ' 
UNION ALL 
SELECT ' test3 ' 
UNION ALL SELECT ' 
test4 ' 
UNION ALL 
Select ' TEST5 ' 
union ALL 
Select ' Test6 ' 
UNION ALL 
SELECT ' test7 ' 
UNION all 
select ' Test8 ' 
select * from Testrestore 

Check the results:

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

Use AdventureWorks 
go 
WAITFOR time ' 21:45 ' 

Now look at the data:

Use AdventureWorks go 
 

At this point, the disaster appears, but remember to be calm.

Here is the focus of this article, do a log backup, the most important thing is to select "Back Up the log tail"

Then on the Options page, select: In addition to the "transaction log", where other red box packages are strongly recommended. and ensure that the database does not have someone else in the connection, because the tail of the backup log causes the database to be in a restored state, denying connections to other sessions and not being backed up if you do not disconnect other connections.

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

Use Master 
go 
BACKUP LOG [AdventureWorks] to DISK = N ' E:\AdventureWorks.bak ' with No_truncate, Noformat, Noinit, NAME = N ' adventureworks-transaction log backup ', SKIP, Norewind, Nounload, NORECOVERY, COMPRESSION, STATS = ten, CHECKSUM 
go 
decl are @backupSetId as int 
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 ') 
if @backupSetId is null begin RAISERROR (N ' validation failed. The backup information for the database "AdventureWorks" could not be found. ', 1] end 
RESTORE verifyonly from DISK = N ' E:\AdventureWorks.bak ' with FILE = @backupSetId, Nounload, norewind
   
    go 



   

At this point, the database is in a "restore" state

If a backup is found, you can view it with the following statement and kill the SPID:

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

Execution results:

and kill it.

Then continue the backup.

Then make a restore, as shown in figure:

To restore the full backup first, select the most recent, due to the characteristics of the log backup (later other articles), only to recognize the last backup, so choose the latest one, otherwise not restore.

Here's another note, remember to choose:

Then restore the log file, which is the most important step:

And then:

Since the experiment was a bit of a problem, back to redo, so the time to choose to 22:19, I was at 22:20 to delete the data. Don't take it too seriously, just assign the time point to the time before you mistakenly delete it. And because the log tail backup is the last backup file, so choose the Red box section here:

Now check again:

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

Summarize:

Usually do not do backups, problems to shout nasty, this is gou have to take, and some people like to see the head of the LDF is very large to delete directly, then the problem will not blame Microsoft.

The method in this article looks a bit cumbersome, but it feels good to do it several times, but the steps suggest strictly follow the above, because once the operation is wrong, it is very troublesome, and then again----calm down and calm down!!!!!!

This approach has several drawbacks:

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

2, this method should be exclusive to the database, so you want to secretly restore is no good. Admit your mistakes bravely.

For a core datasheet, it's a good precaution to see that SQL Server recovers table-level data.

The above is the entire content of this article, I hope to help you learn.