This video provides a quick guide on how to recover data lost due to truncate operation with ApexSQL Recover, a SQL Server recovery tool which uses the information from both database files and backups to recover data and structure lost due to delete, drop and truncate operations.
For more information on these tools, please visit ApexSQL Recover page.
Transcript
Hello, and welcome to another video in ApexSQL disaster recovery video series. In this video we are going to show you how to recover data lost due to truncate operation using ApexSQL Recover, a SQL Server recovery tool which utilizes information stored in the database MDF and LDF files as well as the information from database backup files to recover the data lost due to different SQL Server operations, including delete, drop and truncate.
In order to simulate undesirable truncate table from which we’d like to recover, we’ve prepared a database with a simple table and inserted some generic data. Our database is in the full recovery model which ensures that the transaction log contains full information on transactions which occur on the database which is used as a source for the recovery.
Now, let’s quickly truncate our table
And confirm that the data has been truncated
In order to maximize chances for recovery of truncated data, the time period between the incident and the recovery attempt needs to be minimized. The truncate table operation deallocates the data pages with the existing data and marks them as available for use/overwrite, the actual row data remains intact for the time being. ApexSQL Recover scrapes these deallocated pages for lost data and recovers the truncated data from there. However, deallocated pages can be reused and overwritten by SQL Server at any time, and as the time passes, the chances that SQL Server will overwrite these data pages with new data increase, decreasing the chances for successful recovery thereby.
Since performing a full database backup after the incident doesn’t guarantee a successful data recovery, it is highly recommended to create a copy of databases mdf and ldf files immediately after the truncate. These copies can then be attached to the same or different SQL Server instance as another database which ensures that the information used as the recovery source is not overwritten in the created database copy. This means that we can use ApexSQL Recover on the copy of our database instead on the production database directly, since the recovery source should remain intact there due to the fact that the database is not in the production.
To initiate the recovery process, first we need to start ApexSQL Recover, and to choose the proper recovery option from the main ribbon. In our case, we need to select the ‘recover from truncate table operation’.
Next, we need to select our SQL Server instance, to choose the authentication method and provide valid credentials and finally to select the database where our table was truncated.
In the next step of the wizard, ApexSQL Recover will show a list of all database tables. Here, we need to choose the table that needs to be recovered, so simply ensure that only our table of interest is checked, which is Table_1 in our case.
In the recovery action step of the wizard, the user can make a choice between recovering the dropped table directly to the new database, or creating a recovery script which can later be executed against the original database to complete the recovery. Let’s go with the option to create a recovery script.
After several moments, ApexSQL Recover will complete the processing and provide information on recovered data rows, and allow us to inspect created script by clicking on the ‘View script’ link and ApexSQL Recover will open it in the designated editor which can be chosen in the application options. In our case, we are opening our recovery script in the ApexSQL Recover built-it internal editor.
The only remaining task is to connect the script to our production database and to execute it to recover the truncated data. To do this, first click on the ‘Connect’ button in the main ribbon, and provide full connection details including SQL Server instance, authentication information and choose the production database. Click connect to establish connection. Finally, click on the ‘Execute’ button in the main ribbon, and after short processing, ApexSQL Recover will repopulate truncated data as it was never gone before.
If we check our database with SQL Server Management Studio, we can confirm that the data is safely back in our table.
Thanks for watching. For more information, go to apexsql.com
