SQL Server mis-operation data recovery

For the actual database operation, the data may be encountered in the case of mis-operation, data recovery.

1. Version requirements: SQL Server 2008 and later

2. Full database backup before operation, and no log truncation after backup

The following tests are performed:

1. Set up the test database and select the recovery mode as complete:

2. Make a full backup of the database:

3. Test the tables in the database and insert the test data:

Use test;

Go

CREATE TABLE MyTable

(

Code varchar () NULL,

Name varchar (+) NULL,

Date1 datetime NULL

)

Go

declare @n int, @dateStop datetime

Set @n =1

Set @dateStop = DATEADD (Minute,2,getdate ())

While GETDATE () <= @dateStop

Begin

INSERT INTO MyTable

Select @n,cast (@n as varchar (12)) + ' test ', GETDATE () waitfor DELAY ' 00:00:00:10 '

Set @n = @n + 1

End

4. Revert to: 2018-07-29 10:46:32 data

5. Do the delete operation, the error operation produces

Delete from MyTable

6. Do a transaction log backup:

7. After the misoperation occurs, if it is a production environment, in order to avoid affecting other normal business, using the original full backup done in step 2 to restore, can be restored on the other server, can also be restored on the local to other database names, this example reverts to Test2:

8. Use the transaction log for the restore and select a point in time to restore:

9. Check the data in database Test2, revert to: 2018-07-29 10:46:32:

Precautions:

1. Regular database full backup when daily database maintenance

2. If there is a truncation log operation, should be truncated, immediately do a full database backup, otherwise the log is not contiguous, cannot be restored

3. If the log increment is large, it is recommended to do truncation and backup regularly.

The above summary, for reference. Experience Exchange, +qq:1002732169

SQL Server mis-operation data recovery