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 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 or ApexSQL Recover, 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 file (or Add pattern) in Select data sources step and choose appropriate full backup, transaction log backup(s) and/or detached log files

   

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

   

3. 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 and provide date/time values as close as possible to 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

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

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

   

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

   

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 Recover and/or ApexSQL Log provide 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 Recover or ApexSQL Log. Another option to perform the recovery of deleted SQL Server data using information from transaction logs is to use ApexSQL Recover but using different approach and different resources. ApexSQL Recover 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. This process differentiates in comparison to the previously described one in the fact that for this recovery process, ApexSQL Recover utilizes information inside MDF file in addition to reading from the transaction log files in order to enhance recovery chances in case information in the transaction log files is incomplete.

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

   

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

   

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