US6769074B2 — System and method for transaction-selective rollb…

Claim 1

Independent Claim 128 words

A method of constructing a rollback script used to modify data in a database comprising the steps of: choosing at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; determining an end of a time range for use in terminating searching of the transaction log, searching the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; extracting the at least one log record from the transaction log; computing a command for use in rolling back the operation contained in the at least one log record; and writing the command to a rollback script.

Claim 2

Independent Claim 128 words

A method of constructing a rollback script used to modify data in a database comprising the steps of: choosing at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; determining an end of a time range for use in terminating searching of the transaction log, searching the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; extracting the at least one log record from the transaction log; computing a command for use in rolling back the operation contained in the at least one log record; and writing the command to a rollback script.

Claim 3

Dependent Claim 49 words

The method of claim 1 , wherein the at least one selection criterion is selected from the group consisting of SPID List, Table List, Activity Type, Time Range, and Logical Row Locator, wherein Logical Row Locator is a specific row within a table described by a standard SQL language.

Claim 4

Dependent Claim 39 words

The method of claim 1 , wherein determining an end of a time range further comprises the steps of: issuing a checkpoint command; and reading a log sequence number of a last checkpoint command from a database boot record.

Claim 5

Dependent Claim 34 words

The method of claim 2 , wherein determining an end of a time range further comprises the step of: using the at least one selection criteria to determine the end of the time range.

Claim 6

Dependent Claim 21 words

The method of claim 1 , comprising the step of: accumulating changes needed to rollback each effected row of a table.

Claim 7

Dependent Claim 12 words

The method of claim 5 , wherein the transaction is an Insert.

Claim 8

Dependent Claim 66 words

The method of claim 6 , wherein the changes needed to rollback the transaction comprise the steps of: determining if a row affected by the operation in an input log record was subsequently deleted in the database; and determining that no change is needed to rollback the transaction if the row affected by the operation in the input log record was subsequently deleted in the database.

Claim 9

Dependent Claim 200 words

The method of claim 6 , wherein the changes needed to rollback the transaction comprise the steps of: determining if a row affected by the operation in an input log record was subsequently deleted in the database; maintaining a temporary table that contains row images as they existed at various time points; removing all rows from the table, except for rows of type “B,” whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id; reading a data row directly from a physical database page identified by the Physical Row Id in the input log record; creating a new row in the table of type “D” and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record; and creating a new row in the table of type “B”, and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record.

Claim 10

Dependent Claim 12 words

The method of claim 5 , wherein the transaction is a Delete.

Claim 11

Dependent Claim 74 words

The method of claim 9 , comprising the steps of: removing all rows whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id, except those of type “B”; and creating a new row in the table of type “I” and Physical Row Id equal to a physical row id of the input log record and DataRow equal to a data row of the input log record.

Claim 12

Dependent Claim 12 words

The method of claim 5 , wherein the transaction is a Modify.

Claim 13

Dependent Claim 41 words

The method of claim 11 , comprising the steps of: determining if the table contains a row with the same Row Id as an input log record; and using the row with the same Row Id to perform the Modify transaction.

Claim 14

Dependent Claim 26 words

The method of claim 1 , wherein the at least one selection criterion is Time Range and searching the transaction log comprises using binary search techniques.

Claim 15

Independent Claim 38 words

The method of claims 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 or 13 , wherein the database is a SQL Server database.

Claim 16

Independent Claim 65 words

A method for determining the contents of a row in a database at any point in time comprising the steps of: determining an LSN for the row; searching a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and filtering the at least one log record to determine if the transaction was committed.

Claim 17

Independent Claim 65 words

A method for determining the contents of a row in a database at any point in time comprising the steps of: determining an LSN for the row; searching a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and filtering the at least one log record to determine if the transaction was committed.

Claim 18

Dependent Claim 15 words

The method of claim 15 , wherein the searching is performed in reverse chronological order.

Claim 19

Independent Claim 24 words

The method of claims 15 or 16 , further comprising the step of: determining if the row was relocated to a different physical page.

Claim 20

Dependent Claim 19 words

The method of claim 17 , wherein determining if the row was relocated is done with a pseudo-delete mechanism.

Claim 21

Dependent Claim 75 words

The method of claim 17 , wherein determining if the row was relocated is further comprised of the steps of: analyzing an operation code in the at least one log record; determining if the row has a clustered key; saving the value of the clustered key; and analyzing a set of log records to determine what modification each log record made and if a clustered key in each log record matches the saved clustered key.

Claim 22

Dependent Claim 40 words

The method of claim 17 , wherein determining if the row was relocated is further comprised of the steps of: analyzing an operation code in the at least one log record; and determining if the row has a forward pointer.

Claim 23

Independent Claim 23 words

The method of claims 15 , 16 , 18 , 19 , or 20 , wherein the database is a SQL Server database.

Claim 24

Dependent Claim 14 words

The method of claim 17 , wherein the database is a SQL Server database.

Claim 25

Dependent Claim 151 words

Computer-executable process steps stored on a computer-readable medium, the computer-executable process steps to construct a rollback script used to restore data in a database, the computer-executable process steps comprising: code to choose at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; code to determine an end of a time range for use in terminating searching of the transaction log, code to search the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; code to extract the at least one log record from the transaction log; code to compute a command for use in rolling back the operation contained in the at least one log record; and code to write the command to a rollback script.

Claim 26

Dependent Claim 151 words

Computer-executable process steps stored on a computer-readable medium, the computer-executable process steps to construct a rollback script used to restore data in a database, the computer-executable process steps comprising: code to choose at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; code to determine an end of a time range for use in terminating searching of the transaction log, code to search the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; code to extract the at least one log record from the transaction log; code to compute a command for use in rolling back the operation contained in the at least one log record; and code to write the command to a rollback script.

Claim 27

Dependent Claim 52 words

The computer executable process steps of claim 23 , wherein the at least one selection criterion is selected from the group consisting of SPID List, Table List, Activity Type, Time Range, and Logical Row Locator, wherein Logical Row Locator is a specific row within a table described by a standard SQL language.

Claim 28

Dependent Claim 43 words

The computer executable process steps of claim 23 , wherein determining an end of a time range further comprises: code to issue a checkpoint command; and code to read a log sequence number of a last checkpoint command from a database boot record.

Claim 29

Dependent Claim 36 words

The computer executable process steps of claim 24 , wherein determining an end of a time range further comprises: code to use the at least one selection criteria to determine the end of the time range.

Claim 30

Dependent Claim 23 words

The computer executable process steps of claim 23 , comprising: code to accumulate changes needed to rollback each effected row of a table.

Claim 31

Dependent Claim 15 words

The computer executable process steps of claim 27 , wherein the transaction is an Insert.

Claim 32

Dependent Claim 70 words

The computer executable process steps of claim 28 , wherein the changes needed to rollback the transaction comprise: code to determine if a row affected by the operation in an input log record was subsequently deleted in the database; and code to determine that no change is needed to rollback the transaction if the row affected by the operation in the input log record was subsequently deleted in the database.

Claim 33

Dependent Claim 212 words

The computer executable process steps of claim 28 , wherein the changes needed to rollback the transaction comprise: code to determine if a row affected by the operation in an input log record was subsequently deleted in the database; code to maintain a temporary table that contains row images as they existed at various time points; code to remove all rows from the table, except for rows of type “B,” whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id; code to read a data row directly from a physical database page identified by the Physical Row Id in the input log record; code to create a new row in the table of type “D” and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record; and code to create a new row in the table of type “B”, and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record.

Claim 34

Dependent Claim 15 words

The computer executable process steps of claim 27 , wherein the transaction is a Delete.

Claim 35

Dependent Claim 79 words

The computer executable process steps of claim 31 , further comprising: code to remove all rows whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id, except those of type “B”; and code to create a new row in the table of type “I” and Physical Row Id equal to a physical row id of the input log record and DataRow equal to a data row of the input log record.

Claim 36

Dependent Claim 15 words

The computer executable process steps of claim 27 , wherein the transaction is a Modify.

Claim 37

Dependent Claim 46 words

The computer executable process steps of claim 33 , further comprising: code to determine if the table contains a row with the same Row Id as an input log record; and code to use the row with the same Row Id to perform the Modify transaction.

Claim 38

Dependent Claim 29 words

The computer executable process steps of claim 23 , wherein the at least one selection criterion is Time Range and searching the transaction log comprises using binary search techniques.

Claim 39

Dependent Claim 42 words

The computer executable process steps of claims 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , or 35 , wherein the database is a SQL Server database.

Claim 40

Dependent Claim 82 words

Computer-executable process steps stored on a computer-readable medium, the computer-executable process steps to determine the contents of a row in a database at any point in time the computer-executable process steps comprising: code to determine an LSN for the row; code to search a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and code to filter the at least one log record to determine if the transaction was committed.

Claim 41

Dependent Claim 82 words

Computer-executable process steps stored on a computer-readable medium, the computer-executable process steps to determine the contents of a row in a database at any point in time the computer-executable process steps comprising: code to determine an LSN for the row; code to search a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and code to filter the at least one log record to determine if the transaction was committed.

Claim 42

Dependent Claim 17 words

The computer-executable process steps of claim 37 , wherein the searching is performed in reverse chronological order.

Claim 43

Dependent Claim 25 words

The computer-executable process steps of claims 37 or 38 , further comprising: code to determine if the row was relocated to a different physical page.

Claim 44

Dependent Claim 21 words

The computer-executable process steps of claim 39 , wherein determining if the row was relocated is done with a pseudo-delete mechanism.

Claim 45

Dependent Claim 80 words

The computer-executable process steps of claim 39 , wherein determining if the row was relocated, further comprising: code to analyze an operation code in the at least one log record; code to determine if the row has a clustered key; code to save the value of the clustered key; and code to analyze a set of log records to determine what modification each log record made and if a clustered key in each log record matches the saved clustered key.

Claim 46

Dependent Claim 41 words

The computer-executable process steps of claim 39 , wherein determining if the row was relocated further comprises: code to analyze an operation code in the at least one log record; and code to determine if the row has a forward pointer.

Claim 47

Dependent Claim 25 words

The computer-executable process steps of claim 37 , 38 , 40 , 41 , or 42 , wherein the database is a SQL Server database.

Claim 48

Dependent Claim 16 words

The computer-executable process steps of claim 39 , wherein the database is a SQL Server database.

Claim 49

Independent Claim 152 words

An apparatus for configuring a computerized database restoration system wherein the restoration is performed by executing a rollback script, apparatus comprising: memory means which stores one or more computer-executable process steps; and a processor which executes the process steps so as to: choose at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; determine an end of a time range for use in terminating searching of the transaction log, search the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; extract the at least one log record from the transaction log; compute a command for use in rolling back the operation contained in the at least one log record; and write the command to a rollback script.

Claim 50

Independent Claim 152 words

An apparatus for configuring a computerized database restoration system wherein the restoration is performed by executing a rollback script, apparatus comprising: memory means which stores one or more computer-executable process steps; and a processor which executes the process steps so as to: choose at least one selection criterion for use in determining if a transaction in a transaction log should be rolled back; determine an end of a time range for use in terminating searching of the transaction log, search the transaction log to find at least one log record that satisfies the at least one selection criterion, wherein the at least one log record contains an operation that is part of a committed transaction; extract the at least one log record from the transaction log; compute a command for use in rolling back the operation contained in the at least one log record; and write the command to a rollback script.

Claim 51

Dependent Claim 49 words

The apparatus of claim 45 , wherein the at least one selection criterion is selected from the group consisting of SPID List, Table List, Activity Type, Time Range, and Logical Row Locator, wherein Logical Row Locator is a specific row within a table described by a standard SQL language.

Claim 52

Dependent Claim 35 words

The apparatus of claim 45 , wherein the processor further executes steps so as to: issue a checkpoint command; and read a log sequence number of a last checkpoint command from a database boot record.

Claim 53

Dependent Claim 30 words

The apparatus of claim 46 , wherein the processor further executes steps so as to: use the at least one selection criteria to determine the end of the time range.

Claim 54

Dependent Claim 26 words

The apparatus of claim 45 , wherein the processor further executes steps so as to: accumulate changes needed to rollback each effected row of a table.

Claim 55

Dependent Claim 19 words

The apparatus of claim 49 , wherein the processor further executes steps so that the transaction is an Insert.

Claim 56

Dependent Claim 63 words

The apparatus of claim 50 , wherein the processor further executes steps so as to: determine if a row affected by the operation in an input log record was subsequently deleted in the database; and determine that no change is needed to rollback the transaction if the row affected by the operation in the input log record was subsequently deleted in the database.

Claim 57

Dependent Claim 197 words

The apparatus of claim 50 , wherein the processor further executes steps so as to: determine if a row affected by the operation in an input log record was subsequently deleted in the database; maintain a temporary table that contains row images as they existed at various time points; remove all rows from the table, except for rows of type “B,” whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id; read a data row directly from a physical database page identified by the Physical Row Id in the input log record; create a new row in the table of type “D” and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record; and create a new row in the table of type “B”, and Physical Row Id equal to the input log record's Physical Row Id and DataRow equal to the data row read directly from the physical database page identified by the Physical Row Id in the input log record.

Claim 58

Dependent Claim 19 words

The apparatus of claim 49 , wherein the processor further executes steps so that the transaction is a Delete.

Claim 59

Dependent Claim 79 words

The apparatus of claim 53 , wherein the processor further executes steps so as to: remove all rows whose Physical Row Id and Table Id match the input log record's Physical Row Id and Object Id, except those of type “B”; and create a new row in the table of type “I” and Physical Row Id equal to a physical row id of the input log record and DataRow equal to a data row of the input log record.

Claim 60

Dependent Claim 19 words

The apparatus of claim 49 , wherein the processor further executes steps so that the transaction is a Modify.

Claim 61

Dependent Claim 46 words

The apparatus of claim 55 , wherein the processor further executes steps so as to: determine if the table contains a row with the same Row Id as an input log record; and use the row with the same Row Id to perform the Modify transaction.

Claim 62

Dependent Claim 26 words

The apparatus of claim 45 , wherein the at least one selection criterion is Time Range and searching the transaction log comprises using binary search techniques.

Claim 63

Independent Claim 39 words

The apparatus of claims 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , or 57 , wherein the database is a SQL Server database.

Claim 64

Independent Claim 85 words

An apparatus for determining the contents of a row in a database at any point in time wherein, apparatus comprising: memory means which stores one or more computer-executable process steps; and a processor which executes the process steps so as to: determine an LSN for the row; search a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and filter the at least one log record to determine if the transaction was committed.

Claim 65

Independent Claim 85 words

An apparatus for determining the contents of a row in a database at any point in time wherein, apparatus comprising: memory means which stores one or more computer-executable process steps; and a processor which executes the process steps so as to: determine an LSN for the row; search a transaction log using the LSN in order to identify at least one log record that performed a transaction on the row; and filter the at least one log record to determine if the transaction was committed.

Claim 66

Dependent Claim 20 words

The apparatus of claim 59 , wherein the processor further executes steps so as to search in reverse chronological order.

Claim 67

Dependent Claim 28 words

The apparatus of claim 59 or 60 , wherein the processor further executes steps so as to: determine if the row was relocated to a different physical page.

Claim 68

Dependent Claim 25 words

The apparatus of claim 61 , wherein the processor further executes steps so as to: determine if the row was relocated with a pseudo-delete mechanism.

Claim 69

Dependent Claim 70 words

The apparatus of claim 61 , wherein the processor further executes steps so as to: analyze an operation code in the at least one log record; determine if the row has a clustered key; save the value of the clustered key; and analyze a set of log records to determine what modification each log record made and if a clustered key in each log record matches the saved clustered key.

Claim 70

Dependent Claim 35 words

The apparatus of claim 61 , wherein the processor further executes steps so as to: analyze an operation code in the at least one log record; and determine if the row has a forward pointer.

Claim 71

Dependent Claim 23 words

The apparatus of claim 59 , 60 , 62 , 63 , or 64 , wherein the database is a SQL Server database.

Claim 72

Dependent Claim 14 words

The apparatus of claim 61 , wherein the database is a SQL Server database.

Annuity Stage	Window Opens	Payment Deadline	Surcharge Period Until	Estimated Fee	Status
3.5 Years (Year 4)	2007-07-27	2008-01-26	2008-07-27	$2000.00	Window Closed / Lapsed
7.5 Years (Year 8)	2011-07-27	2012-01-26	2012-07-27	$3760.00	Window Closed / Lapsed
11.5 Years (Year 12)	2015-07-27	2016-01-26	2016-07-27	$7700.00	Window Closed / Lapsed

System and method for transaction-selective rollback reconstruction of database objects

Abstract

Inventors

Bibliographic Details

Claims Analysis

References Cited (Backward Citations)

USPTO Annuity & Maintenance Windows

Assigned Categories & Tags

Assign Category Tag