SQL Server database log backup and recovery steps

-- 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 #