Recovering data with SQL Server logs

If you are already anxious, see this article, please change a posture, deep breaths several times, calm down to read this article, perhaps your problem solved.

I met the situation is that the site was planted Trojan, stole my web. config file, the Web. config file inside the database connection string is not encrypted, and my database remote connection did not do IP restrictions, hackers through the database client connected to my database, Delete all the tables, so make sure you have a good habit of encrypting the database connection string or restricting the IP of the remote Access database .

Because the hacker deleted all the data at a specific point in time I do not know, so I used a tool log Explorer to view the Analysis database log, to see when the specific is deleted (if you already know the point of time to restore, you do not use this tool) , as for the Log Explorer How to install the use of, here do not repeat, to Baidu a search a large heap, install when the client and server are selected, and then the installation will have a step to let you enter the database user name and password, other do not understand the crazy next bar.

Through the Log Explorer tool, I can see that the time that was deleted is: 07-10 09:46:29.880, then I'll restore the data to 07-10 09:46:29.879.

Here's how to get back to work.

1, back up the database log, with T-SQL statements

1	BACKUP LOG shua TODISK=‘c:\shua_log.bak‘ WITHFORMAT

2, because my database has automatic full backup every day, find a full backup of the most recent time, to restore, here I do not cover the old library, directly restored to a new database shua2

Using the SQL Server Management Studio Tool for interface operation, write SQL statement restore is too complex, not ah.

To restore the database, right-click on the database, is the completion of my [RESTORE Database] interface->[general] tab page

Then click on the Options tab page and Note that this step is very important .

Click "Do not perform any operations on the database, do not roll back the committed transactions", and then determine. If the database is relatively large, this restore to wait a long time, go out to suck a few fresh air, the merit will be gaocheng.

3. After the database restore is successful, this state

Display (Restoring ... ), restore the transaction log to the SHUA2, which is the database right-click Task.

If you fill in, first fill in the beginning of the backup log C:\shua_log.bak, and then tick [restore], the most important thing is to choose a point in time, but this point of time can not select milliseconds, so I chose 29 seconds, this time 29 seconds ago, and my data is deleted from 29.88 seconds, so choose 29 seconds is duly completed; why didn't I choose 28 seconds? Because my database changes very much every second, I try to narrow this timeframe as much as possible.

After filling it out, click "OK" and you are done. Now that the database has been restored to the state of the 2014-07-10 09:46:29.0, naturally my table data is back.

Recovering data with SQL Server logs