Skip to main content

 

Druva Documentation

Restore a SQL Server database using transaction log

Phoenix Editions: File:/tick.png Business File:/cross.png Enterprise File:/tick.png Elite

Restoring the database to a point-in-time when a transaction occurred

Phoenix backs up transaction log, and you can use it to restore your database to a point-in-time. It is a useful, lightweight approach to backup and restore. Transaction log backups are incrementally triggered after a full backup job has completed, and a restore point exists. The transaction log is backed up every few minutes based on your settings. Phoenix uses the restore point, and the transactions that were logged, to perform a point in time restore.  For more information on how the point in time restore works, see SQL Server Point in time restore workflow.

To execute a point in time restore:

  1. Log on to the Phoenix management console.
  2. On the menu bar, click Servers.
  3. Select the server you want to backup.
  4. On the Servers page, click Restore > Point in Time Restore.
  5. On the Restore to point in time window, select a restore location and click Next.
    You can restore to Original MS-SQL server location, or select another MS-SQL server instance. If you select the original server instance, specify a folder on the instance. If you select another MS-SQL server instance, specify the server, instance, and location on which you want to restore your database. 
  6. On the restore to point in time window, select a date and time based on the transactions that were backed up.

    After you specify date, time, and choose the standby mode option, click Next.
  7. The Restore to point in time window lets you choose the databases that use the identified transaction. Select the databases and click Done.

Restoring the database to a marked transaction

In addition, you can restore your database if you mark a transaction in SQL server. You can use the marked transaction to restore your database. Phoenix lets you choose a marked transaction to perform a point in time restore. The marked transaction restore, like the point in time restore using log backup, lets you restore your databases to a consistent state till a particular transaction occurs that updates your databases.  Marked transaction restores use log backups that are incrementally triggered after a full backup job has completed, and a restore point exists.  

To restore your database using a marked transaction

  1. Log on to the Phoenix management console.
  2. On the menu bar, click Servers.
  3. Select the server you want to backup.
  4. On the Servers page, click Restore > Transaction Mark Restore.
  5. On the Restore to a Transaction Mark window, you can choose to restore original server instance or an alternate server instance.
    If you choose original instance, provide full path of the folder where you want to restore your database and click Next.
    If you choose an alternate instance, provide the server, instance, and full folder path to restore your database and click Next.
  6. The Restore to a Transaction Mark window lets you choose the marked transactions you can use to restore your database to a specific transaction. In addition, the restore options Stop Before Mark and Stop At Mark let you include the marked transaction in your restore job or exclude it.
    After you select the transaction and the restore option, click Next.

In the next step, select the databases that use the marked transaction. Select the databases you want to restore, and click Done.

Restoring your database to a point-in-time with standby mode enabled

When you restore your database using transaction log, Phoenix provides an option to leave your database in the standby mode (steps 6 of the above procedure). If you select the option to leave your database in standby mode, you can restore your database to another point-in-time using transaction logs. 

For example, when you are on step 6, you are notified that you can restore your database to any point in between May 19, 2015 9:32:21 PM and May 26, 2015 06:24:38 PM, and you specify May 23, 2015 01:00:00 PM as the time to restore your database. To restore the database:

  • Phoenix identifies and selects the transaction closest to May 23, 2015 01:00:00 PM.
  • Transaction identified as closest to the time you specify can be the transaction logged right before the time you specify or, the transaction that was logged at the exact specified time.

If the option Leave database in Standby mode to allow restore of transaction logs is enabled,

  • You can specify another date and time and restore your database to a transaction that occurred after May 23, 2015 01:00:00 PM.
  • The transaction logs after the specified date and time for restore are downloaded. You can use the downloaded logs to restore your database to a point in time.

In step 6:

  • If you entered May 23, 2015 01:00:00 PM as the date and time, and May 23, 2015 12:52:15 PM is the transaction closest to it
  • Your database is restored to the transaction at May 23, 2015 12:52:15 PM, and
  • You have downloaded logs that you can use to restore your database up to May 23, 2015 17:57:00 PM.

Use the following command to apply logs:

Restore log [DB_name] from disk=’log location’ with standby=’path_to_standy_file’, stopat=’timestamp’

Where timestamp format is: 2015-05-23 17-57-00.

If the standby mode is not enabled:

  • Your database is restored to the transaction that was logged closest to the time you specified.
  • Your database is made operational.
  • All subsequent transaction logs are deleted, and there is no way you can restore your database to a transaction that was logged after the time you specified. In this example, you cannot restore your database to a transaction that was logged after May 23, 2015 01:00:00 PM.

Point-in-time restore workflow

Following flow-chart depicts the point-in-time restore workflow using transaction logs: