SQL Server database corruption recovery with transaction log backups
The worst-case scenario a DBA can encounter is a SQL Server database data-file corruption (due to physical or some other occurrence, the data files can be damaged and inaccessible)
Most SQL DBAs have recovery plans for this scenario – creating regular backups and using them later to restore the database to the nearest available point in time, prior the disaster. However, this is not always the case. Even with regular backups, there will always be a missing portion of data between the last backup and the time of the disaster. With ApexSQL Log, it is possible to recover all the data to the point of failure, if the transaction log file (.ldf) is not damaged
The requirements for successful SQL database recovery in this scenario are:
- The database was in the full recovery model and the transaction log has not been truncated after the last backup
- The full log backup and uninterrupted transaction log chain are available:
- Ensure that the online transaction log file has not been truncated since the last backup, or
- Have available all transaction log backups up to the point of the truncation (the truncation is done automatically when creating transaction log backups)
To recover the data, you have to do the following:
- Make sure you have saved the online transaction log (.ldf) file
- Restore the database using the full database backup and all available consecutive transaction log backups:
-
Open SQL Server Management Studio and select the “Restore” option. Select “Device” as the source, click the ellipsis button and add the latest full backup and all available transaction log backups:
- Leave all options on their defaults and click “OK”. Wait for the restore process to finish
-
Start ApexSQL Log, connect to the newly recovered database and click “Next”
- On the Select SQL logs to analyze screen click the “Add” button and locate the original online transaction log file (.ldf). Deselect the online transaction log file and select the newly added detached transaction log. It is also possible to add both full and transaction log backups here, but in this particular scenario, it is not needed
-
Click “Next”
-
After clicking “Next”, there are two options. You can either select “Open results in grid” if you need to review the changes before restoring them to the database, or you can select “Undo/Redo” to create the script that will restore the records directly, without reviewing them first.
-
On the Filter setup screen, select the “Whole transaction log”
-
Select the operations filter and select all schema operations (DDL). This step can be skipped if you are sure there have been no updates to the table schemas since after the last backup was created
Using the grid view to further review and refine the restored data
After selecting “Open results in grid” the data will be reconstructed and displayed in the grid. If you are satisfied with the data and want to restore everything, simply click “Create redo script” in the ApexSQL Log menu
Use the newly created redo script to restore your database to the state just prior to disaster
Exporting the data directly to a file
After selecting “Export results to file”, the Export to file screen will be shown. Select REDO (reconstruct) script, specify the output file location and click Finish
After data reconstruction is finished, the redo script file will be ready for database restoration
Using the REDO script to restore the database
After the REDO script is generated by using either of the described options, you need to execute it against the newly restored database. You can use SQL Server Management Studio, ApexSQL RunScript utility, or any other integrated developer environment. SQL Server Management Studio has a limitation on the script size and may not be used to execute large scripts
To use the ApexSQL RunScript utility, download it from here: ApexSqlRunScript
Open Command prompt where the ApexSQL RunScript utility is downloaded and execute it using the following syntax:
ApexSqlRunScript.exe <server_instance_name> <database_name> <script_filename>
e.g.
ApexSqlRunScript.exe (local)\SQL2012 RecoveredDatabase c:\Temp\RedoScript.sql
After the script is executed, the summary information will be displayed and your database will be restored to the point in time just before the disaster happened
Useful resources
MSDN – Restore and Recovery Overview (SQL Server)
MSDN – File Restores (Full Recovery Model)
October 11, 2013
