Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to recover the historical data of SQL Server through the log". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to recover the historical data of SQL Server through the log".

Through log restore, the most important thing is:

1. There must be a full backup, and the backup must be made before the data is modified or deleted.

two。 After updating and deleting the data, make a log backup, which will be used to restore the previous data.

The purpose of the following steps: restore the deleted table

In the SQL Server Management visualization procedure:

1. Create and make a full backup:

-create a test database

CREATE DATABASE Db

GO

-- backup the database

BACKUP DATABASE Db TO DISK='c:\ db.bak' WITH FORMAT

GO

two。 Create an empty table

-- create a test table

CREATE TABLE Db.dbo.TB_test (ID int)

3. Delete the empty table you just created, assuming that the table has been deleted by mistake

Suppose we delete the Db.dbo.TB_test table by mistake now

DROP TABLE Db.dbo.TB_test

At this point, we want to restore the deleted TB_ Test table. At this point, remember the time before deleting the table, which will be used later.

At this time, you need to back up the log. You can use the SQL Management interface to operate the backup, or you can use T-SQL to back up.

BACKUP LOG Db TO DISK='c:\ db_log.bak' WITH FORMAT

GO

4. Restore the database, you can replace the original database, or restore to a new database DB1, where the new database DB1

If it is an interface operation:

Tasks-restore-Database:

Select our previous full backup: db.bak after the device in the General selection page

Target database: DB1

Under recovery status, select item 2 "do nothing on the database and do not roll back committed transactions." no, no, no. " After the point is determined, you can see that the state of the DB1 database becomes "restoring."

Next: right-click on the DB1 database-- restore-- transaction log, and select the transaction log we just backed up after we deleted the table: db_log.bak.

Select the time below, which is the time before deleting the table that we remember above. Click OK, and the restore is successful. You can see that the deleted table has come back.

Complete script:

-create a test database

CREATE DATABASE Db

GO

-- backup 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 before performing subsequent operations (this is because the maximum time accuracy of SQL Server is 3% seconds. If there is no delay, it may cause the operation to restore to the time point to fail.)

WAITFOR DELAY '00VOUR 01'

GO

Suppose we delete the Db.dbo.TB_test table by mistake now

DROP TABLE Db.dbo.TB_test

-- time to save the deleted table

SELECT dt=GETDATE () INTO #

GO

-- after the delete operation, it was found that the table Db.dbo.TB_test should not be deleted

The following demonstrates how to restore 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 will first restore the full backup (restoring the log must be carried out on the basis of restoring 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 above delete time and is slightly earlier than the delete time

DECLARE @ dt datetime

SELECT @ dt=DATEADD (ms,-20,dt) FROM #-gets the time that is slightly 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 watch is restored

SELECT * FROM Db.dbo.TB_test

/ *-result:

ID

-

(the number of rows affected is 0)

-- * /

-- successful test

GO

Finally, delete the test environment we did.

DROP DATABASE Db

DROP TABLE #

Thank you for reading, the above is the content of "how to recover the historical data of SQL Server through the log". After the study of this article, I believe you have a deeper understanding of how to recover the historical data of SQL Server through the log, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.