Recover a SQL Server database using an old backup and the current transaction log file

How to recover a SQL Server database using an old backup and the current transaction log file DBAs have various disaster recovery plans. One of the questions they have to answer before setting up a right plan is how much data can they afford to lose. Many of “accidental DBAs” become aware that making regular full database backups might not be enough, only after it’s too late.

Say you create a full database backup every Friday after work hours, your database MDF files are located on one hard disk, the LDF on another and the backups on the third one and your MDF file disk crashed on Friday morning. Thanks to wisely distributing the files across 3 different disks, you have a full database backup and a healthy LDF file.

What SQL Server recovery options do you have?

1. The simplest and at the same time the worst option is to restore the most recent full database backup, created last Friday. You’ll lose almost 7 days of data. If you can afford such a loss, go ahead with this scenario

2. Try to back up the LDF file, i.e take a tail-log backup. A tail-log backup captures any log records that have not yet been backed up. Note that you can do this even for a database which is offline. It will succeed only if the LDF file is not damaged

   BACKUP log database_name TO DISK = 'disk:\location' WITH init, no_truncate;

   If the backup succeeds, restore the full database backup first using the RESTORE WITH RECOVERY option and then restore the tail-log backup.

   The downside of this method is that it’s only applicable to full or bulk-logged recovery models and that creating a tail-log backup can fail.

3. Use ApexSQL Log or ApexSQL Recover
   

   ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed. ApexSQL Recover also includes the same mechanisms which allow it to read transaction log files and rollback unintended changes.

   If your database is in the Full recovery model and you made no transaction log backups after the last full database backup, it means that all the transactions that occurred after the full database backup are still in the LDF file and there’s a good chance of data and object recovery. But even if your database is in the Simple recovery model, there is a chance to recover your SQL Server database.

   1. Use the most recent full database backup and restore it
   2. Start ApexSQL Log or ApexSQL Recover

   3. Connect to the database

      

   4. In the next step, add salvaged LDF file via Add file button and check it

      

   5. In same step Add the full database backup used to restore the database. This is necessary for successful reconstruction of UPDATEs. Click Next.

   6. In the select output windows choose Open results in grid

      

   7. In the Filter setup, specify the time range you want to recover

      

   8. Click Finish

   9. The transactions read from the LDF file will be shown in the grid

      

      To create a replay script, in the Actions group in the menu, click Redo.

   10. When the script is created, execute it against the database restored using the old backup. All the transactions read from the LDF file will be added

SQL Server database recovery to a point in time is possible even without transaction log backups. Use ApexSQL Log or ApexSQL Recover to read detached SQL Server transaction logs and replay or rollback the transactions read.