Why ApexSQL Recover?

Every DBA has at one point or another been faced with the challenge of recovering lost data. There are many ways in which data can be lost, such as DELETEs , TRUNCATE TABLEs , DROP TABLES , database corruption or even the very common UPDATE without the WHERE clause.

Of course we all know that there are various techniques to limit your exposure when these events occur, such as backups or database snapshots etc. But then there are those occasions when Murphy rules and nothing goes according to plan. And you have no backup. Maybe your job failed and you didn’t notice, maybe it was a new database that was just never added to the backup plan to begin with, maybe the dog ate it, whatever the reason…..don’t panic!
In this article I will show you, how ApexSQL Recover can help you to get your data back (and keep your job)

ApexSQL Recover is a complete recovery solution for SQL Server.

ApexSQL Recover applies a variety of data recovery techniques which guarantees the best possible result. If your data is still on the disk ApexSQL Recover will find it. Not only does it rifle through your online transaction log and database file, but it can also look through older data or log backups, and detached files.

But wait! There’s more.

ApexSQL Recover can also extract BLOB data into files as well as extract table structure and data from backup files without restoring those backups.

Here’s how ApexSQL Recover can help you

ApexSQL Recover offers the following functionality:

• Data recovery from a DELETE operation

  Delete operations are fully logged in the transaction log, they are also not necessarily immediately deleted from the data files. Therefore, ApexSQL Recover could obtain this data both by reading the log and / or extracting it from the data pages which ever provides the better result.

• Data recovery from INSERT and UPDATE operations

  When database is in the full recovery model, information on all executed INSERT and UPDATE information will be stored in the transaction log files indefinetly or until log files are manually truncated. This means that ApexQL Recover can be used forensically to roll back any INSERT or UPDATE changes and get the data back to it’s original state.

• Data recovery from a TRUNCATE operation

  When a TRUNCATE TABLE statement is executed in SQL Server, the pages containing the data are immediately de-allocated, which basically means that they have been marked for re-use. SQL Server logs a TRUNCATE TABLE statement in the online transaction log fully, but it logs it efficiently by logging only the page allocations, making it hard to use the transaction log to extract the data after a TRUNCATE TABLE has occurred. For this reason the main technique used by ApexSQL Recover to recover this data, is finding the de-allocated pages in the data files and checking if the data is still available.

• Data recovery from a DROP TABLE operation

  ApexSQL Recover is able to recover both the schema and the data for a dropped table. When selecting this option, ApexSQL Recover will offer to recover structure only, data only, or both. Like truncate operations, drop table operations are efficiently logged. The table structure may be obtainable from the transaction log, but data needs to be recovered from the data files.

• Object recovery from the DROP *OBJECT* operation

  Similar to the previously mentioned recovery solutions, information on dropped objects is also stored inside the transaction log file, and ApexSQL Recover can be used to recover dropped object.

• Deleted BLOB recovery

  Binary Large Objects (BLOB) are not stored in SQL Server the same way as regular data, because of the size of the object this type of data inevitable requires multiple pages to be stored. This is can be either as row over flow data (varbinary, nvarchar, sqlvariant, varchar types) or LOB data (text, ntext or image types). When such an object has been lost it cannot be found in the Transaction log and as such ApexSQL Recover has to extract this type of data from the data files.

• Data extraction from a database backup without having to restore it

  Technically this is not really a recovery feature, but if you have ever had to wait hours to restore a backup just get a single table back, this is definitely for you. If you only need to recover a couple of tables from a backup, this could save you an enormous amount of time. Instead of having the restore the entire backup, which could take hours and take up a significant portion of your available disk space, you could simply extract the table structure and/or data from the backup directly without restoring it.

• BLOB extraction into various formats

  Also not a recovery feature, BLOB extraction allows you to extract all your BLOB data from your database into actual files. This is useful because BLOB data is not returned in a readable format when executing a SELECT statement.
  ApexSQL Recover will run through your entire database and export all BLOBs as files with a .blob file extension to your selected location.

As you can see, ApexSQL Recover provides different options depending on how the data was lost in order to reduce the recovery time. If you do not know how the data was lost or you are not having any success with the option selected, you may try all the options. Because the options use different methods for recovering the data, you may have lesser or more success with a different option. To maximize successful recovery as many additional data sources should be provided as possible.

The rate of successful recovery depends on various factors, such as:

• The recovery model of your database
• When the data was lost
• How soon after the incident you attempt the recovery
• What additional data sources, such as previous backups, log backups, detached data files etc, you have available

Because time may be of the essence we highly recommend that if critical data is involved you immediately make a copy of the database files (.mdf, .ndf, .ldf). This may involve taking the database offline temporarily. If this is not at all possible, you may still attempt the recovery on the live database.

Typically recovered data can be extracted into script or into a new database.

Some technical information for the discerned

… or the insane who want to recover their data manually 😉

In this section I will explain some basic details of how data deletion is stored and managed in SQL Server to give you some insight as to how ApexSQL Recover is able to recover data which is no longer visible in SQL Server, or how you might even attempt to perform this task yourself.

SQL Server is optimized for performance. This means that it will perform tasks in the most efficient way possible to reduce the time is takes to do them.

When a row is deleted from SQL Server it is not in actual fact removed immediately, this is for a couple of reasons, but mainly for efficiency. As such instead of completely removing the row, the row is simply marked as being a ghost. This means that the row will no longer be taken into account for any queries executed against the table, and will be cleaned up later by the ghost clean up task. Let’s look at a simple example of this:

-- Create a simple table with a primary key
CREATE TABLE lost_objects (id INT IDENTITY(1,1) PRIMARY KEY , name_e VARCHAR(20))

--Insert some rows into it
INSERT INTO lost_objects VALUES ('keys'),('cellphone'), ('wallet')

-- Get the page ID in this case it’s 178
SELECT allocated_page_file_id , allocated_page_page_id 
FROM sys.dm_db_database_page_allocations(db_id('AdHoc'), object_id('dbo. lost_objects), null, null, 'DETAILED')

--Now lose your keys
DELETE FROM dbo.lost_objects
WHERE id = 1;

-- Redirects the output to SSMS instead of Error Log
DBCC TRACEON(3604); 
GO
--Look at the page header 
DBCC PAGE(AdHoc, 1, 178, 1);

GO

If you look at the page header immediately after a delete, you will notice that it indicates a number of ghost records on that page. In this case one.

In the OFFSET TABLE you can see that the record still appears to be there, but because it was ghosted it will be completely ignored by any query, except in the case where a rollback is issued. If a rollback is issued SQL Server just marks the row as no longer being a ghost and it will be available again immediately.

The next time you look at that page you may find that the ghost record count has been set back to 0 (the ghost cleanup task ran in the meantime).

But this does not mean that the data is no longer there, if you look at the page again using DBCC PAGE with print option 2 (which includes the whole hex dump), you will see that even after you deleted your record, it still exists.

DBCC PAGE(AdHoc, 1, 178, 2);

This is just the relevant portion of the hex dump to indicate that the data is still there:

But if you look at the row offset you will see that according to SQL Server that row is no longer there.

You will also notice by looking at the hex dump, that the data can be quite difficult to make out.

Reading this data from the data pages is merely one of the techniques that ApexSQL Recover utilizes to recover lost records. It reads the database data file, finds the ghost records (records which have been logically but not physically deleted), and creates an INSERT script for them. But the faster you act the better. Because even though the data is still there, the pages may have been de-allocated, which means that SQL Server will overwrite them as soon as it needs that space for something else.

In the case of data which have been fully logged in the transaction log such as a DELETE operation, assuming that the log has not yet been truncated, ApexSQL Recover may also use that technique to recover your deleted data. Let’s have a look at what the data in the online Transaction Log looks like when I execute the following script:

CHECKPOINT
GO
DELETE FROM dbo.lost_objects
WHERE id = 2;

Now let’s look at the Log by issuing the following command:

SELECT	L.[Current LSN], 
		L.Operation,
		L.Context , 
		L.Description,
		L.[Transaction ID], 
		L.[Begin Time], 
		L.[RowLog Contents 0],
		L.[RowLog Contents 1],
		L.[Log Record]
FROM fn_dblog(NULL,NULL) L

As you can see there is a lot of information for this simple transaction such as the transaction ID, when is started and ended, the type of operation the log sequence number. But probably most important in the case of recovery will the RowLog Contents columns. There are five of these columns returned with this function, but not all of them will contain data, the column used depends on the type of operation performed. Since the deleted data is displayed in hexadecimal, you will also need to know exactly what format has been used, the number of columns involved etc.

Since this function is not officially documented getting the information required to reconstruct data from it is not an easy feat.

Conclusion

What makes ApexSQL Recover so invaluable is the fact that even though it is possible for you to find this missing data yourself as demonstrated above (if you had the time , the know-how and the inclination), ApexSQL spent years perfecting its unique algorithms and recovery techniques to make it the most effective data recovery tool on the market today.

With ApexSQL Recover you can look through online, detached databases and log files to ensure the most complete recovery from any data disaster with a couple of clicks, a fraction of the time it would take you to do this yourself. ApexSQL Recover saves you time, money and your job!