How to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operations (4213014)

How to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operations

You’ve inadvertently executed (or experienced other data loss scenarios involving) a DELETE, TRUNCATE or DROP statement against a database and lost valuable records.

How can you bring the data back?!?! Scenarios: a) Easy:

• No changes occurred after the DELETE, TRUNCATE or DROP statements and you have a full backup

>> Restore a previous full database backup b) Hard:

• You have a full backup but …
• Some changes were made after the DELETE, TRUNCATE or DROP statements, or
• You cannot take the database offline

>> Use the full database backup to export the data and import it into the production database c) Impossible?

• You don’t have a full database backup that contains the lost data
• You cannot take the database offline
• Some transactions were made on the database after the data was lost

>> Use ApexSQL Recover How can ApexSQL Recover help? The transaction log keeps the information about the transactions made to your database ApexSQL Recover is a tool that can mine the database transaction log and recover deleted, dropped and lost data.

1. Start ApexSQL Recover
2. Select a recovery option based on the statement executed e.g. Recover lost data due to a DELETE operation If you don't know which statement caused the data loss, use all options, one after another If the data loss was caused by two or more different statements, use the adequate options, one after another
   
3. First, connect to the database and provide valid credentials
   

4. If you have transaction log backups or detached transaction logs created at the time when the data was lost, click Add transaction logs to add them. In case there were many transactions on the database after the data was lost, the information about the lost data might not exist in the online transaction log anymore. By adding these additional sources, you will provide the necessary information about the transactions that removed the data from your database. Use Help me decide option if unsure what additional data sources can be used.

   This option is available only for the DELETE and DROP TABLE options.

   If additional sources are not available, select No additional transaction logs are available and go to step #7

   

5. Add a sequence of transaction log backups that end with the online transaction log and/or detached transaction logs using the Add file button

   

6. Select the time when the data was lost. This will narrow down the results only to the records you want to recover. You don’t want to insert the data you have deleted deliberately

   If you’re not sure when this was, select Date and time are unknown

7. Select the table(s) from which the data was lost. Check out your database structure to find if the tables you want to recover have any relations. Make sure that all tables that depend on the tables being recovered are also selected, to avoid problems with re-inserting dependent data

   

1. Select Save recovery script to file to create a redo T-SQL script that will insert all the recovered records. You can review and modify the script before you execute it

   

After the Undo script is executed, the lost data is inserted into the table

So, remember, even if you have unintentionally deleted table records, truncated or dropped a table, there is a chance to recover the lost data using ApexSQL Recover