Shulou(Shulou.com)06/01 Report--

Recently, the book "Microsoft SQL Server 2012 Internals" has been studied, considering how to quickly recover mismanipulated data, such as UPDATE, DELETE, TRUNCATE, DROP, and so on. When the database is very large, it becomes very difficult to restore mismanipulated data by restoring the database.

So how to recover data quickly without restore database. This will also be mentioned in this article.

First of all, briefly understand the principle of DROP TABLE operation.

1. Delete the DDL of a table

two。 Delete data page data

Through the analysis of Transaction Log, we can see that drop table does not log the deletion of each row of data, and drop table ultimately marks the table's data pages as rewritable to indicate free space (when there is not enough space, these data pages will be format)

When the database space is insufficient, SQL Server can write data to this space.

Therefore, if we want to recover data without restore database, we have to make sure that the data page of the table after drop table is not format. Once the data page is format, it can only be recovered through restore database (no other recovery method has been found yet).

The following is an example of a restore table structure statement

1. Build a table

Create table test_drop (col1 tinyint,col2 smallint,col3 int identity (1), col4 bigint,col5 varchar (20), col6 char (20), col7 nvarchar (20), col8 nchar (20), col9 datetime,col10 timestamp,col11 uniqueidentifier,col12 sysname,col13 numeric (10) 2), col14 xml,col15 money,col16 text)

two。 Delete tabl

Drop table test_drop

3. Restore deleted table structure statements

Exec Recover_Dropped_Table_DDL_Porc 'test_drop'

Generate the recovery statement as follows:

If object_id ('dbo.test_drop') is not null print' dbo.test_drop is existed'elsecreate table dbo.test_drop (col1 tinyint null, col2 smallint null, col3 int identity, col4 bigint null, col5 varchar (20) collate SQL_Latin1_General_CP1_CI_AS null, col6 char (20) collate SQL_Latin1_General_CP1_CI_AS null, col7 nvarchar (20) collate SQL_Latin1_General_CP1_CI_AS null, col8 nchar (20) collate SQL_Latin1_General_CP1_CI_AS null, col9 datetime null Col10 timestamp not null, col11 uniqueidentifier null, col12 sysname collate SQL_Latin1_General_CP1_CI_AS not null, col13 numeric (10je 2) null, col14 xml null, col15 money null, col16 text collate SQL_Latin1_General_CP1_CI_AS null)

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.