Reply from log to database

Reply to database from log -- create Test Database  
Create Database DB  
Go  
 
-- Back up the database  
Backup database dB to disk = 'C: \ dB. Bak' with format  
Go  
 
-- Create a test table  
Create Table dB. DBO. tb_test (id int)  
 
-- Delay 1 second, and then perform subsequent operations (this is because the SQL server has a time precision of up to 3% seconds. If there is no delay, the Restoration Operation to the time point may fail)  
Waitfor delay '00: 00: 01'  
Go  
 
-- Assume that the table dB. DBO. tb_test is deleted by mistake.  
Drop table dB. DBO. tb_test  
 
-- Save the time when the table was deleted  
Select dt = getdate () #  
Go  
 
-- After the delete operation, the database. DBO. tb_test table cannot be deleted.  
 
-- The following shows how to restore the accidentally deleted table dB. DBO. tb_test  
 
-- First, back up transaction logs (transaction logs can be restored to the specified time point)  
Backup log dB to disk = 'C: \ db_log.bak 'with format  
Go  
 
-- Next, we need to restore the full backup first (the restoration log must be performed on the basis of 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 earlier than the deletion time  
Declare @ DT datetime  
Select @ dt = dateadd (MS,-20, DT) from # -- get a time earlier than the time when the table was deleted  
Restore log DB from disk = 'C: \ db_log.bak 'with recovery, stopat = @ dt  
Go  
 
-- Check whether the table is restored.  
Select * from DB. DBO. tb_test  
 
/* -- Result:  
ID  
-----------  
 
(The number of affected rows is 0)  
--*/  
 
-- Test successful  
Go  
 
-- Delete the test environment.  
DROP DATABASE DB  
Drop table #   -- Create a Test Database  
Create Database DB  
Go  
 
-- Back up the database  
Backup database dB to disk = 'C: \ dB. Bak' with format  
Go  
 
-- Create a test table  
Create Table dB. DBO. tb_test (id int)  
 
-- Delay 1 second, and then perform subsequent operations (this is because the SQL server has a time precision of up to 3% seconds. If there is no delay, the Restoration Operation to the time point may fail)  
Waitfor delay '00: 00: 01'  
Go  
 
-- Assume that the table dB. DBO. tb_test is deleted by mistake.  
Drop table dB. DBO. tb_test  
 
-- Save the time when the table was deleted  
Select dt = getdate () #  
Go  
 
-- After the delete operation, the database. DBO. tb_test table cannot be deleted.  
 
-- The following shows how to restore the accidentally deleted table dB. DBO. tb_test  
 
-- First, back up transaction logs (transaction logs can be restored to the specified time point)  
Backup log dB to disk = 'C: \ db_log.bak 'with format  
Go  
 
-- Next, we need to restore the full backup first (the restoration log must be performed on the basis of 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 earlier than the deletion time  
Declare @ DT datetime  
Select @ dt = dateadd (MS,-20, DT) from # -- get a time earlier than the time when the table was deleted  
Restore log DB from disk = 'C: \ db_log.bak 'with recovery, stopat = @ dt  
Go  
 
-- Check whether the table is restored.  
Select * from DB. DBO. tb_test  
 
/* -- Result:  
ID  
-----------  
 
(The number of affected rows is 0)  
--*/  
 
-- Test successful  
Go  
 
-- Delete the test environment.  
DROP DATABASE DB  
Drop table #