SQL Server Data Recovery

Time:2020-11-21

1. Recover the database from the log

–Create test database 
  CREATE   DATABASE   Db   
  GO   
    
— backup the database 
  BACKUP   DATABASE   Db   TO   DISK=’c:\db.bak’   WITH   FORMAT   
  GO   
    
— 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′   
  GO   
    
— 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   #   
  GO   
    
— 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   
  GO   
    
— 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   
  GO   
    
— 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]   
  GO   
    
— check to see if the table is restored 
  SELECT   *   FROM   Db.dbo.TB_test   
    
/ * — result: 
  ID                       
  ———–     
    
(the number of rows affected is 0) 
  –*/   
    
— test successful 
  GO   
    
— 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

https://download.microsoft.com/download/E/2/1/E21644B5-2DF2-47C2-91BD-63C560427900/NDP452-KB2901907-x86-x64-AllOS-ENU.exe

 

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.

Link: https://pan.baidu.com/s/1onssFmFuN7F21sSaM5JO1g
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.