SQL Server Data Recovery

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