Only three lines of script, SQL data recovery to a specified point in time

Often see someone mistakenly delete the data, or misoperation, especially update and delete the time without adding where ... Then shouted father Shout Niang, afraid of parents also helpless shook shoulder.

In other words, if you have not made a mistake, then you are still engaged in the process of it?! Without the occasional heartbeat, destined for a lifetime of loneliness ah haha ....

OK, the book to the story, to share my treatment plan at the time, and gentlemen mutual encouragement, welcome to spit Groove, a lot of communication.

Encountered this situation, generally do not do backup, or backup is not timely (for example, my company's data, there is no real-time backup, but fortunately, I 25th data Misoperation, the team leader in 19th did a backup, fortunately, Oh! ), or else it will not come to the question. First of all , be calm , otherwise there will be greater trouble.

However, don't worry, tell you now, three rows of SQL script, data recovery to a specified time. Sir, please your eyes.

The first thing you need to know is what your data recovery model is. Specific methods: Select the operation's database, "Properties"-"Options"-"recovery mode", whether the selection is "complete."

Make sure there is a full library backup (or there are multiple differential or incremental backups) before the Misoperation, and the first thing to do is to go into a log backup (trunc If you want to keep the log file from getting bigger). Log on chkpt. Option 1 Then you're dead.

Backup database Demo to disk= ' D:\db.bak '

ensure that the recovery model of the database is at least "simple". As for how to change into a full model , this should not be necessary to say more.

Or use a script check:

Select Recovery_model,recovery_model_desc

From sys.databases

where name = ' AdventureWorks '

Then, make sure that no one else links the database , and then make a log tail backup

Look at this script that is too late to chop hands ...

UPDATE person SET gender= ' female '

Before Operation: >>After Operation:

If this is a customer table, uh,,

Of course, I believe you are unintentional, anyway, calm, calm, believe me, this time, the boss will not fire you, but, you catch chicken is to quickly retrieve the lost data.

Only three lines of script, SQL data recovery to a specified point in time

--1, first, back up the transaction log (the restore log must be based on restoring a full backup.) Use transaction logs to restore to a specified point in time )

BACKUP LOG Demo to disk= ' D:\\db_log.bak ' with FORMAT

--2, Next, we want to restore the full backup first (the restore log must be on the basis of restoring a full backup)RESTORE DATABASE Db from disk= ' C:\db.bak ' with Replace,norecovery3, restore the transaction log before the delete operation (the time here corresponds to the above delete time, and a little earlier than the deletion timeRESTORE LOG Db from disk= ' C:\db_log.bak ' with recovery,stopat= '--gets a little earlier than the time the table was deletedIf you find that the backup can not be viewed with the following statement, and the SPID killed: SELECT * from sys.sysprocesses WHERE dbid=db_id (' Demo ') --Check to see if the table is restoredSELECT * from Demo.person

PS, in the operation of these scripts, the recommendation is done under the master database, very good, to solve the data recovery

After all: usually do not do backup, out of trouble to catch chickens, this is your own! If your company is like this, it is recommended that you leave quickly, a company that does not pay attention to the data, you have to keep the meaning.

However, no one can avoid some mistakes in the work, the key is our ability to solve problems. But as veteran comrades, at this time, not to you think of how many kinds of solutions, your circle of friends how many drinking buddies can ask for help, there are many ways the great God for support ...

You only need to do two things: 1, 30 seconds seriously think about what you have done before and after the operation;

In 2, 15 seconds to your immediate leadership to report truthfully.

There are several drawbacks to this approach (it is fabricated here and cannot be found by the author):

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 sheet, the prevention operation should be done first.

Only three lines of script, SQL data recovery to a specified point in time