SQL Server database log backup and recovery steps
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 #
