MSSQL Recover Dropped Table without Restore DB (i)

Recently in the study of Microsoft SQL Server Internals, this book considers how to quickly recover misoperation data such as update, DELETE, TRUNCATE, drop, and so on. When the database is particularly large, it can be very difficult to restore the data by restoring the database.

So how to recover data quickly without restoring the database. This will also be what this article is going to mention.

First, simply understand the principle of the drop table operation

1. Delete the DDL for the table

2. Delete Data page data

By analyzing the transaction log, the drop table does not record the log that deletes each row of data, and the drop table is eventually overwritten by marking the table data page as a means of freeing up space.

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

Therefore, if we want to recover the data without restoring the database, we have to make sure that the data page of the table after the drop table has not been overwritten. Once the data page is overwritten, it can only be restored by restoring the database.

The following is an instance of the Restore table structure statement

1. Build a table

CREATE TABLE Test_drop (col1 tinyint,col2 smallint,col3 int identity (), col4 bigint,col5 varchar (+), col6 char (20), Col7 nvarchar (), Col8 nchar, col9 datetime,col10 timestamp,col11 uniqueidentifier,col12 sysname,col13 Numeric ( 10,2), col14 xml,col15 money,col16 text)

2. Delete a table

drop table Test_drop

3. Restore the deleted table Structure statement

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 ' Create table dbo.test_drop (col1 tinyint null ,col2  smallint null ,col3 int identity ,col4 bigint null ,col5  varchar ( COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,COL6 CHAR)  collate  sql_latin1_general_cp1_ci_as null ,col7 nvarchar ( collate sql_latin1_general) _cp1_ci_as null ,col8 nchar ( COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL&NBSP); Col9 datetime null ,col10 timestamp not null ,col11 uniqueidentifier  null ,col12 sysname collate sql_latin1_general_cp1_ci_as not null , Col13 numeric (10,2)  null ,col14 xml null ,col15 money null ,col16  text collate&nbsP sql_latin1_general_cp1_ci_as null )

MSSQL Recover Dropped Table without Restore DB (i)