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

Phoenix backs up the 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.

Phoenix allows you to restore databases to the original MS-SQL server location, or select another MS-SQL server instance. You can also restore the database to an AG. The restored database is replicated across the primary and secondary nodes of the AG.

To execute a point-in-time restore

Restore to the original server in case of an availability group (AG), restores the database to the instance of the primary server.

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click Protect > MS-SQL Servers.
  3. On the Instances/AGs tab, click the instance or availability group you want to restore. 
  4. On the MS-SQL page, click Restore > Point in Time Restore.
    You can restore to the 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. You can also restore the database to an AG. The restored database is replicated across the primary and secondary nodes of the AG.
  5. On the Restore to point in the time window, click one of the following options to restore the database and click Next.

Restore to original SQL server instance

  1. In the Select restore location section, in the Specify Location box, type or select the absolute path to the restore location, and click Next
    Restore_point_in_time_original_specify_location.PNG
  2. In the Select time to restore databases section, select a date and time based on the transactions that were backed up.
    After the restore operation, Phoenix lets you leave the database in one of the following modes:
    • Recovery mode: In this mode, Phoenix rolls back all the uncommitted transactions and keeps the database in the ready state. Phoenix also ensures that all the data sets restored are consistent with the database and reports errors in case of inconsistencies.
    • No recovery mode: In this mode, Phoenix does not roll back any uncommitted transactions. The database is not usable in this intermediate, non-recovered state. This mode is used to restore a database backup and multiple transaction logs, or when you need multiple RESTORE statements.
    • Standby mode to allow restore of additional transaction logs: In this mode, Phoenix allows a database to be brought up for the read-only access between the transaction log restores. This mode is used in the warm standby server situations or special recovery situations to inspect the database between the log restores.
  3. Click the respective mode to leave the database in after the restore operation:
    • Recovery mode:
      1. Click Recovery mode and click Next.
        The Databases section lists the databases that you can restore to the specified date and times.
      2. Select the database(s) to restore and click Finish.
    • No recovery mode:
      1. Click No recovery mode and click Next.
        The Databases section lists the databases that you can restore to the specified date and time.
      2. Select the database(s) to restore and click Finish.
    • Standby mode:
      1. Click Standby mode to allow restore of additional transaction logs.
      2. In the Download logs till section, specify another date and time to restore your database to a transaction that occurred
      3. In the Specify location box, type or select the absolute path to the restore location, and click Next.
        For more information about the point-in-time restore with the standby mode, see Restoring database to a point-in-time with standby mode enabled.
        The Databases section lists the databases that use the identified transaction.
      4. Select the databases and click Done.

Restore to alternate SQL server instance

  1. In the Restore to alternate SQL Server Instance section, enter the details in the following fields:
    • Select server: From the Select server list, select the MS-SQL server to which you want to restore the database.
    • Select instance: From the Select instance list, select the instance of the selected MS-SQL server to which you want to restore the database.
    • Specify location: In the Specify Location box, type or select the absolute path to the restore location, and click Next.
      SQL_PIT_Restore_alternate_server_instance.PNG
  2. In the Select time to restore databases section, select a date and time based on the transactions that were backed up.
    After the restore operation, Phoenix lets you leave the database in one of the following modes:
    • Recovery mode: In this mode, Phoenix rolls back all the uncommitted transactions and keeps the database in the ready state. Phoenix also ensures that all the data sets restored are consistent with the database and reports errors in case of inconsistencies.
    • No recovery mode: In this mode, Phoenix does not roll back any uncommitted transactions. The database is not usable in this intermediate, non-recovered state. This mode is used to restore a database backup and multiple transaction logs, or when you need multiple RESTORE statements.
    • Standby mode to allow restore of additional transaction logs: In this mode, Phoenix allows a database to be brought up for the read-only access between the transaction log restores. This mode is used in the warm standby server situations or special recovery situations to inspect the database between the log restores.
  3. Click the respective mode to leave the database in after the restore operation:
    • Recovery mode:
      1. Click Recovery mode and click Next.
        The Databases section lists the databases that you can restore to the specified date and timestamp.
      2. In the Databases section, select the database(s) to restore and click Finish.
    • No recovery mode:
      1. Click No recovery mode and click Next.
        The Databases section lists the databases that you can restore to the specified date and timestamp.
      2. In the Databases section, select the database(s) to restore and click Finish.
    • Standby mode:
      1. Click Standby mode to allow restore of additional transaction logs.
      2. In the Download logs till section, specify another date and time to restore your database to a transaction that occurred
      3. In the Specify location box, type or select the absolute path to the restore location, and click Next.
        For more information about the point-in-time restore with the standby mode, see Restoring database to a point-in-time with standby mode enabled.
        The Databases section lists the databases that you can restore to the specified date and time.
      4. Select the database(s) to restore and click Finish.

Restore to Availability Group

Phoenix allows you to add the restored database(s) to an AG. The restored databases are replicated across all the primary and secondary nodes in the AG. To use this feature, upgrade the Phoenix client to version 4.7.6 or later.

  1. In the Select restore location section, provide the details in the following fields:
    • Select Availability Group: From the Select Availability Group list, select the AG to restore the database. A database cannot be restored to an AG if there is no AG configured or the configured AG has an older client version. 
    • Shared Network Location: In the Shared Network Location box, type or select the absolute path to the network location to synchronize the database across the primary and secondary nodes in the AG. Ensure that the shared network location is accessible to all the nodes in the AG. The Shared Network Location is a required field for SQL Server 2012 and SQL Server 2014, whereas it is an optional field for SQL Server 2016.
    • Restore Location: In the Restore Location box, type or select the absolute path to the restore location.
      Restore_AG_PIT.PNG
  2. Click Next.
  3. In the Select time to restore databases section, select a date and time based on the transactions that were backed up and click Next.
    The Databases section lists the databases that you can restore to the specified date and timestamp.
  4. Select the database(s) to restore and click Finish.

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

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

Transaction_logs_standby.PNG

For example, when you are on step 3, you are notified that you can restore your database to any point between August 05, 2018, 01:48:43 PM and August 06, 2018 07:29:30 PM and you specify August 06, 2018, 07:29:30 PM as the time to restore your database. To restore the database:

  • Phoenix identifies and selects the transaction closest to August 06, 2018, 07:29:30 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 to restore your database to a transaction that occurred after August 06, 2018, 07:29:30 PM.
  • The transaction logs that are logged 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 3, if you entered August 06, 2018, 07:29:30 PM, and August 06, 2018, 07:20:00 PM is the transaction closest to it,

  • Your database is restored to the transaction on August 06, 2018, 07:20:00 PM, and
  • You have downloaded logs that you can use to restore your database up to August 06, 2018, 07:29:30 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 the timestamp format is: 2018-08-06 07-29-30.

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 the 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 August 06, 2018, 07:29:30 PM.

Point-in-time restore workflow

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

SQL_AG_Restore_Workflow.png

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 until 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.  

Restore to original server in case of an AG, restores the database to the instance of the primary server.

MS­-SQL server does not replicate transaction marks related to the transaction logs on the secondary nodes for databases in AG. If you try to restore a transaction mark for an AG backup set from Phoenix, you might or might not see transaction marks that were created on the MS-SQL server depending on whether the log backup happened from the primary or secondary node. 

To restore your database using a marked transaction

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click Protect > MS-SQL Servers.
  3. Select the instance or availability group 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 the 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.