1. Recover the database from the log
–Create test database
CREATE DATABASE Db
— backup the database
BACKUP DATABASE Db TO DISK=’c:\db.bak’ WITH FORMAT
— create test table
CREATE TABLE Db.dbo.TB_test(ID int)
— delay for 1 second, and then perform the following operation (this is because the maximum time precision of SQL server is 3% seconds. If there is no delay, the operation to restore to the time point may fail)
WAITFOR DELAY ’00:00:01′
— let’s assume that we’ve deleted it by mistake Db.dbo.TB_ Test this table
DROP TABLE Db.dbo.TB_test
— when to save the deleted table
SELECT dt=GETDATE() INTO #
— after the delete operation, it was found that the table should not be dropped Db.dbo.TB_ test
— the following shows how to recover this mistakenly deleted table Db.dbo.TB_ test
— first, back up the transaction log (use the transaction log to restore to the specified point in time)
BACKUP LOG Db TO DISK=’c:\db_log.bak’ WITH FORMAT
— next, we need to restore the full backup first (the restore log must be done on the basis of restoring the full backup)
RESTORE DATABASE Db FROM DISK=’c:\db.bak’ WITH REPLACE,NORECOVERY
— restore the transaction log to before the delete operation (the time here corresponds to the deletion time above, and is slightly earlier than the deletion time)
DECLARE @dt datetime
Select @ DT = DateAdd (MS, – 20, DT) from
RESTORE LOG Db FROM DISK=’c:\db_log.bak’ WITH RECOVERY,[email protected]
— check to see if the table is restored
SELECT * FROM Db.dbo.TB_test
/ * — result:
(the number of rows affected is 0)
— test successful
— finally, delete our test environment
DROP DATABASE Db
DROP TABLE #
2. Use tools to recover
Apexsqllog2016 cracked version, but when the cracked version reads the log file, it is easy to crash. You need to be patient and try it several times (available for personal testing).
1、 It’s a good ideaDo not back up the database, because some database settingsIn “simple mode”, the backup will delete the log and cause the recovery to fail.
2、 It’s a good ideaDetach the database first
3、 It’s a good ideaFind the database file path and copy a copy for backup. (for insurance, make sure it’s backed up)
4、 It’s a good ideaAttach the detached database back
5、 To install the software running environment, you need to.NET 4.5。 The following is the official path, you can rest assured to download
6、 Set the database compatibility level to 100。 Here isSQLsentence
ALTER DATABASE [Database name] SET COMPATIBILITY_LEVEL = 100
7、 Start using software ApexSQLLog2016 Need to run as Administrator
8、 Select the to restoreSQLConnections and databases
9、 Wait for database and log lookup
wait a while The following interface will appear
Suggest saving as a .sqlFile, and then open it for operation.
If the restored data contains Single quotation mark’’ There may be errors in the execution of the statement. Just change it manually.
Extraction code: wujw
There is no problem using it under win7. Server 2008 cannot be used. It may be a C + + environment problem, which can not be solved.
The server can download the genuine version, but the function is limited.