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

This article will explain in detail how to recover MSSQL data through the log. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Set up database and test table

Create database zpcreate table [zping.com] (id int, name varchar (20))

2. Insert test data

Insert into [zping.com] (id,name) values (1memzping.com1') insert into [zping.com] (id,name) values (2recoverzping.com2') insert into [zping.com] (id,name) values (3recoverzping.com3') insert into [zping.com] (id,name) values (4herozping.com4')

To back up the database, you can use the visual operation of sql server. The following is the generated code: [code] BACKUP DATABASE [zp] TO DISK = NumberC:\ k.bak' WITH NOFORMAT, NOINIT, NAME = Numberzp-full database backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO [code]

View the data:

Note: at this point we make a full backup

At this point, some data may have been accidentally deleted. Let's simulate this: (suppose you delete the data of 1 ~ # ~ 2)

Deletefrom [zping. Com] where (id=1orid=2)

Note: keep in mind the approximate deletion time

At this time found that the deletion error, how to get the data back ah. There are two methods:

1. Recover data through Log Explorer search (this tool is available on my website)

2. Another method restores data by restoring the log (a specified point-in-time recovery).

Description: the first method can be operated online.

The second method must stop the database or restore another database (provided there must be a full backup and log backup)

Here we will discuss the second method of specifying point-in-time recovery:

1. Back up the database transaction log at this time (note that you cannot do a transaction log backup without a full backup of the database)

At this time, create a new database zp (rename the previous database) and restore the database

At this point, we see that there are two restored database backups, because I backed up the zp database twice, and both backups have the same data files. Here we choose the most recent backup.

The default settings in the database are as follows: append to the backup set, so there will be two backups, as shown below:

At the same time, set the "do not roll back" transaction in options.

Note: if you restore the database from the transaction log, you must select do not roll back the transaction.

Once determined, the following occurs:

At this time, it is found that the database has been "being restored", and the database transaction log is restored.

1. Select the time in "General" (the time just deleted)

2. Set the recovery status to "rollback uncommitted" transactions in "options"

After confirming, query the database and find that the data has come back.

Summary:

1. This is a method for data security of general large websites, because the database is relatively large (there may be hundreds of gigabytes of data), it takes a long time to make a full backup, and it affects the performance of the database server. In order to ensure data security, most of them use full backup + transaction log backup to ensure data security. For example, do a full backup once a day or every 2 days, and a log backup within a few hours. (of course, you can do this by writing a job.)

2. For example, the mirroring in SQL server 2005 uses this method of transaction log synchronization to ensure data synchronization.

3. If the recovered log data appears "LSN" too early and too late, it indicates the discontinuity between transaction logs. At this point, pay attention to the timing and order of backups.

On how to recover MSSQL data through the log to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.