[SQL Server] data restored from logs

Document 1 (zhujian)

 

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

 

 

Document 2 (Htl)

 

SQL transaction log restoration instance favorites
Use master
Go
-- Create a Test Database
Create Database db_test go

-- Back up the database
Backup database db_test
To disk = 'C:/db_test.bak'
With format
Go

-- Create a test table
Create Table db_test.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_test.dbo.tb_test is deleted by mistake.
Drop table db_test.dbo.tb_test
Go

-- After the delete operation, the table db_test.dbo.tb_test cannot be deleted.

-- The following shows how to restore the accidentally deleted table db_test.dbo.tb_test.

-- First, back up transaction logs (transaction logs can be restored to the specified time point)
Backup log db_test
To disk = 'C:/db_test_log.bak'
With format
Go

-- Get the time range that can be tried
If object_id (n'tempdb .. # ') is not null
Drop table #
Create Table #(
Backupname nvarchar (128 ),
Backupdescription nvarchar (255 ),
Backuptype smallint,
Expirationdate datetime,
Compressed tinyint,
Position smallint,
Devicetype tinyint,
Username nvarchar (128 ),
Servername nvarchar (128 ),
Databasename nvarchar (128 ),
Databaseversion int,
Databasecreationdate datetime,
Backupsize numeric (20, 0 ),
Firstlsn numeric (25, 0 ),
Lastlsn numeric (25, 0 ),
Checkpointlsn numeric (25, 0 ),
Databasebackuplsn numeric (25, 0 ),
Backupstartdate datetime,
Backupfinishdate datetime,
Sortorder smallint,
CodePage smallint,
Unicodelocaleid int,
Unicodecomparisonstyle int,
Compatibilitylevel tinyint,
Softwarevendorid int,
Softwareversionmajor int,
Softwareversionminor int,
Softwareversionbuild int,
Machinename nvarchar (128 ),
Flags int,
Bindingid uniqueidentifier,
Recoveryforkid uniqueidentifier,
Collation nvarchar (128 ),
Familyguid uniqueidentifier,
Hasbulkloggeddata bit,
Issnapshot bit,
Isreadonly bit,
Issingleuser bit,
Hasbackupchecksums bit,
Isdamaged bit,
Beginslogchain bit,
Hasincompletemetadata bit,
Isforceoffline bit,
Iscopyonly bit,
Firstrecoveryforkid uniqueidentifier,
Forkpointlsn numeric (25, 0) null,
Recoverymodel nvarchar (60 ),
Differentialbaselsn numeric (25, 0) null,
Differentialbaseguid uniqueidentifier,
Backuptypedescription nvarchar (60 ),
Backupsetguid uniqueidentifier null
)
Insert # exec (n'
Restore headeronly
From disk = ''c:/db_test.bak''
With file = 1
Restore headeronly
From disk = ''c:/db_test_log.bak''
With file = 1
')
-- Select
--*
-- From #

-- Defines the time range and interval of the attempt.
Declare
@ Start_date datetime,
@ Stop_date datetime,
@ Try_step_millsecond int,
@ MSG nvarchar (1000)
Select
@ Start_date = min (backupfinishdate), -- try to find the start time of the accidentally deleted data
@ Stop_date = max (backupfinishdate), -- try to find the end time of the accidentally deleted data
@ Try_step_millsecond = 500 -- query data at a time point every 500 milliseconds
From #

-- Restore full backup
Restore database db_test
From disk = 'C:/db_test.bak'
With replace,
Norecovery

-- Restore the log backup to each point to find the required data
While @ start_date <@ stop_date
Begin
Select
@ Start_date = dateadd (MS, @ try_step_millsecond, @ start_date ),
@ MSG = n' time point of attempt: '+ convert (varchar (50), @ start_date, 121)

Raiserror (@ MSG, 10, 1) with Nowait
Begin try
-- Restore the log to a specified point and use standby to enable read-only access to the database
Restore log db_test
From disk = 'C:/db_test_log.bak'
With stopat = @ start_date,
Standby = 'C:/db_test_redo.bak'

-- Check whether the required data exists
If object_id (N 'db _ test. DBO. tb_test ') is not null
Begin
-- Display the restoration time point
Select restoreto = @ start_date
-- Restore the database to make the database readable and writable
Restore log db_test
With recovery

Select
@ Start_date = @ stop_date
End
End try
Begin catch
End catch
End
Go

-- Delete the test environment
Drop database db_test
Drop table #

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/04/03/4041741.aspx

 

Document 3 (dawugui)

 

/*
Title: Log Explorer for SQL Server v4.0.2 and instructions for use
Author: AI xinjue Luo Xiaohua
Time: 2008-07-14
Location: Urumqi, Xinjiang
Source: http://topic.csdn.net/u/20090305/00/849723bf-74ad-495f-8fc6-22d2108beb10.html? Seed = 1633183628
*/

1. lumigent Log Explorer for SQL Server v4.0.2 Special Edition
Http://down.chinaz.com/soft/7887.htm
Log Explorer for SQL Server 4.2 registration code
Wv5rc-uxvpz-e33-nr4694qs2

2. Log Explorer for SQL Server v4.0.2
After installation, register the program (the compressed file has a registration machine)
Open Log Explorer File => attach log file-> Select Server and logon mode-> connect->
Select database> attach> browse> view log> In the left-side dialog box to view the log record.
Click View DDL commands. There are many drop table commands.
Click the "undo" button below to generate the table structure Statement (Create Table ....)
Click the "Salvage" button below to generate the insert Statement (insert into... values ....)
To restore the file, right-click the log record undo transation and choose Save file name and path> open the file and run the T-SQL code in the query analyzer.

For example, if log is delete table where..., the generated file code is Insert table ....

Several Problems in Log Explorer
1) complete, differential, and log backup of the database
Deleted non-active entries in the transaction log are selected during Backup.
When I try to view logs with Log Explorer
Prompt NO log recorders found that match the filter, wocould you like to view unfiltered data
Select Yes and you will not be able to see the record.
If you do not select to delete non-active entries in the transaction log
Log Explorer can be used to view logs.
2) modified part of the data in one of the tables. At this time, Log Explorer is used to view the logs, which can be used for log recovery.
3), and then restore the backup. (Note: The restoration is to disconnect Log Explorer from the database or connect to other data,
Otherwise, the database is in use and cannot be recovered)
After the restoration, Log Explorer prompts no log recorders found that matches the filter, wocould you like to view unfiltered data
If yes is selected, the log record modified in 2 is not displayed, so the restoration cannot be performed.
4) do not use the SQL backup function to back up your logs.
The correct backup method is:
Stop SQL Service, copy data files and log files for file backup.
Start the SQL service and use Log Explorer to restore data.
5) if the Log Recovery Model of your database is simple, Log Explorer cannot be used for recovery.
6) Log Explorer must be installed on the SQL Server server on which the database is to be restored, or the server must be installed on the SQL Server server, and the client must be installed on the operating computer for data recovery.

3. If the data volume is large, a genuine Veritas is installed on the tape drive and cluster, which is a good way to restore data.
The following are important new features of the software:
1) Disaster recovery drill (disaster recovery fire drill)-the ability to freely test, plan, and test disaster recovery plans without interrupting the production process.
2) Cluster simulator (cluster simulator)-test the application failover solution to verify the availability of the application and check whether the application has failover policies and application requirements as planned, migrate to the most appropriate server.
3) Global Cluster option-when the availability requirement is migrated from local to wide area disaster recovery, it can be quickly and easily upgraded to any architecture.
4) instant access to replicated data-the ability to instantly access data while replicating data only occupies part of the customer's available storage capacity.
5) Volume Replicator Advisor: accurately analyzes bandwidth requirements and ensures application optimization.

4. How to restore data:
1) at ordinary times, you need to perform hot backup, daily backup, monthly backup, annual backup, data replication, exception records, and other work, so that you can be impatient when data is lost.
2) If the disk damage is incorrect or the database is accidentally deleted, you can use software such as easyrecovery or recover4all to restore the deleted or damaged files and recover the data.
3) if the network is disconnected from the replication, we recommend that you only manually create a batch of data to make up for the loss of data, generally, data similar to latitude (such as time and region) is selected.