Multiple SQL Server data recovery options

In the past two weeks we had some interesting Solution center articles describing Microsoft SQL Server topics – each described as the problem (or the challenge) users may encounter with SQL Server data recovery

Any of recovery options using Microsoft’s SQL Server Management Studio requires the availability of a full database backup. And, the backup must be restored in order to be used as a recovery data source

On the other hand, ApexSQL Log, a SQL server transaction log reader and ApexSQL Recover, a SQL data recovery software, use additional alternative methods for digging out corrupted or lost data – reading the SQL Server transaction log and the MDF file of a live database

Restore of a database to a point in time

Several common situations require the restore of a database to a point in time type of SQL data recovery:

  • Data corruption caused by a user
  • A database upgrade failure
  • A hardware media failure

To repair this, i.e. perform the restore to a point in time using ApexSQL Log, you’ll need to:

  1. Connect to the database which will be restored and add any SQL transaction log backups and/or detached transaction logs containing the missing/corrupt data. This should create a full chain of transaction log backups. To do that, click Add in Data sources step
  2. In the same step click Add to provide a full database backup which will be used as a starting point from which a full chain of transactions starts

    Select SQL logs to analyze

  3. In the Select output options dialog click the Open results in grid

    Filter setup - Date/Time range selection

  4. Using the Filter setup and the Time range section, specify the point in time which is a target for the recovery process – using the Custom option, by specifying only the To value as a target. This will narrow down the search and reading process to exactly required time point

All transactions which will be rolled back are shown in the ApexSQL Log’s main grid when the process finishes

ApexSQL Log's main grid

Use the Create undo script option on the main toolbar to open the Undo script dialog. It will provide a resulting undo script which may be executed immediately on the connected database, or saved for later use or analysis

Resulting undo script

For a small number of records, the method with the results shown in the grid is acceptable. But when it comes to millions of deleted records, it is recommended to create an undo script without showing the results in the grid

  1. Instead of Open, in the Result viewing options dialog, click Undo/Redo

    Click on Export results to a file

  2. Select Undo (Rollback) script and specify the file path and name

    Export results - Generate Rollback Script

The undo script that inserts all deleted records back in their tables will be created

Comparing to the recovery performed via SQL Server Management Studio, ApexSQL Log provides multiple criteria filters and details about each transaction; more importantly:

  • The database and transaction log backups being processed are not being applied/rolled back to the database but read, analyzed and only the final undo script is being applied, so the process is faster
  • During the process, the database being restored is available as a live database

Recover deleted SQL data from transaction logs

We have previously described how deleted records can be recovered using ApexSQL Log. Another option to perform the recovery of deleted SQL Server data using information from transaction logs is to use ApexSQL Recover. It has less filtering options, and cannot provide an individual transaction in a form ApexSQL Log does with its main grid. On the other hand, it provides more SQL recovery output options. It can also create an Undo SQL script that inserts records, but it can also create a new database and use it as a recovered data destination. The following steps perform deleted data recovery using ApexSQL Recover:

  1. Select data recovery From DELETE operation option
  2. Specify the SQL Server instance, credentials and database where the records have been deleted
  3. Select Add transaction logs
  4. Provide any additional resources using the Add backup and Add detached buttons. Note that the transaction log backups must form a full chain
  5. Specify the time when the delete statement was executed. If you don’t know when this happened, select Date and time are unknown
  6. Use the table filter to narrow down the recovery to just specific tables, if applicable
  7. The final result is a script that inserts the deleted data into desired database. Check it out and execute

Recover deleted SQL data from a backup or from online database files

We have learned how a backup or an online database may be used in order to perform SQL data recovery using ApexSQL Recover. Another ApexSQL tool which can use these resources to retrieve deleted SQL Server data is ApexSQL Data Diff. It is a SQL database comparison and synchronization tool which detects data differences. It can compare and synchronize live databases and native or natively compressed SQL database backups and generate comprehensive reports on the detected differences

So, using ApexSQL Data Diff, we can use as a source either another online database or a backup which contains deleted data. The process is the same

  1. Select, for example, a database backup that contains deleted records as a source
  2. Select the online database, from which the data was deleted, as a destination

    Selecting the online SQL database, from which the data was deleted

  3. After comparing, the tables with different records are shown in the ApexSQL Data Diff main grid
  4. Select the tables you want to synchronize. The selection is available on a column level also, so if there are records you don’t want to synchronize, simply uncheck them

    Selecting the SQL tables for the synchronization process

  5. Click Synchronize on the menu and go through the steps in the Synchronization wizard

July 5, 2013