SQL Server database log backup and recovery steps _mssql

--Create a test database
CREATE DATABASE Db
Go
--Backing 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 seconds, and then do the following (this is because SQL Server's time precision is 3% seconds, without delay, may cause restore to point in time the operation failed)
WAITFOR DELAY ' 00:00:01 '
Go
--Suppose we now mistakenly delete the Db.dbo.TB_test this table
DROP TABLE Db.dbo.TB_test
--Time to save the deleted table
SELECT dt=getdate () into #
Go
--After the delete operation, it is found that the table Db.dbo.TB_test should not be deleted
--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 a specified point in time)
BACKUP LOG Db to disk= ' C:\db_log.bak ' with FORMAT
Go
--Next, we need to restore a full backup (the restore log must be based on a full backup)
RESTORE DATABASE Db from disk= ' C:\db.bak ' with Replace,norecovery
Go
--Restores the transaction log before the delete operation (the time here corresponds to the deletion time above and slightly earlier than the deletion time)
DECLARE @dt datetime
SELECT @dt =dateadd (MS,-20,DT) from #--Gets a little earlier than the time the table was deleted
RESTORE LOG Db from disk= ' C:\db_log.bak ' with recovery,stopat= @dt
Go
--Check to see if the table is restored
SELECT * from Db.dbo.TB_test
/*--results:
Id
-----------
(The number of rows affected is 0 rows)
--*/
--Test success
Go
--Finally delete the test environment we did
DROP DATABASE Db
DROP TABLE #