SQL Server database log backup and recovery steps

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.