Skip to main content
Druva Documentation

Restore a SQL server database using transaction logs

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

Phoenix backs up SQL transaction logs and lets you restore your databases to a point in time. Transaction log backups are triggered after a full backup job completes, and a restore point exists. Transaction logs are backed up every few minutes based on your backup schedule settings. Phoenix uses the restore point and the logged transactions to perform a point-in-time restore. See the Point-in-time restore workflow to know more.

Phoenix allows you to restore databases to a point in time to:

Restore the database to the original SQL server instance

When you perform a point-in-time restore of a database to its original location, you can:

When you restore a database on a standalone instance to its original location, you can restore it as a copy or you can replace the database on the SQL server instance with the restored database. If you replace the original database, Phoenix terminates connections to the database on the original instance and then replaces the database with the restored database.  When you restore a database on an availability group (AG) to the original location, Phoenix restores the database to the instance of the primary server.

Restore  as a copy

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instances and availability groups.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. On the All SQL Resources page, click the standalone instance or availability group that hosts the database you want to restore.
  5. From the standalone instance or availability group details page, under the Configured Backup Sets section, select the backup set and click Restore, or
    You can also search for the database from the SQL Backup Sets page using the search box. Select the database, and click Restore.
    Search for database to restore.png
  6. In the Restore Type, select Point in Time Restore.

    Point in time restore.png
  7. In the Select Date and Time section, you can see the time range of the latest transaction log backup by default. You can also see the databases corresponding to this restore point. Select the point-in-time to which you want to restore the databases, and then click Show Databases. Phoenix restores the databases to the last available committed transaction before the specified point in time.
  8. Select the database(s) and click Restore.
  9. In the Restore Target page, select Restore to original SQL server instance, and click Next.

    Restore to point in time - restore to original instance.png
  10. In the Restore Settings page, select Restore as a copy and perform the following tasks:

    Restore to point in time - restore as a copy.png
    1. Restore MDF, LDF, NDF files to different locations: Select this checkbox to restore the MDF, LDF, and NDF files of the database to different locations on the original server. If this option is selected, the option to browse or enter an absolute path to the MDF, LDF, and NDF file locations is enabled. Enter the absolute path to MDF, LDF, and NDF files or browse to the locations.
    2. Restore Location: Enter the absolute path to the restore location. You can also browse to the path. The database files are restored to the location you specify. This field disappears if the Restore MDF, LDF, MDF files to different locations option is selected.
    3. Under After restore, leave databases in, select one of the following:
      1. 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.
      2. 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
      3. Standby mode to allow restore of additional 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. If you select this mode. perform the following tasks:
        • Download Logs till: You can download the transaction logs between the time frame specified above this field. Select a date and time.
        • Restore Location: Enter an absolute path of the location where you want to restore additional transaction logs. You can also browse to that location.
  11. Click Finish. After the restore job completes, Phoenix leaves the database in the selected mode.

Replace original database

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instances and availability groups.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. On the All SQL Resources page, click the standalone instance or availability group that hosts the database you want to restore.
  5. From the standalone instance or availability group details page, under the Configured Backup Sets section, select the backup set and click Restore, or
    You can also search for the database from the SQL Backup Sets page using the search box. Select the database, and click Restore.
    Search for database to restore.png
  6. In the Restore Type, select Point in Time Restore
  7. In the Select Date and Time section, select the point in time to which you want to restore the databases. Databases will be restored to the last available committed transaction before the specified point in time. 
  8. Click Show databases to see all the databases that can be restored to the point in time. 
  9. Select the database(s) and click Restore.
  10. In the Restore Target page, select Restore to original SQL server instance, and click Next.

    Restore to point in time - restore to original instance.png
  11. In the Restore Settings page, select Replace original database.

    Restore to point in time- Replace original database.png
  12. Under After restore, leave databases in, select one of the following:
    1. 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.
    2. 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
    3. 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. If you select this mode, perform the following tasks:
      1. Download Logs till: You can download the transaction logs between the time frame specified above this field. Select a date and time.
      2. Restore Location: Enter an absolute path of the location where you want to restore additional transaction logs. You can also browse to that location.
  13. Click Finish. After the restore job completes, Phoenix leaves the database in the selected mode.

Restore to alternate SQL server instance

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instances and availability groups.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. On the All SQL Resources page, click the standalone instance or availability group that hosts the database you want to restore.
  5. From the standalone instance or availability group details page, under the Configured Backup Sets section, select the backup set and click Restore, or
    You can also search for the database from the SQL Backup Sets page using the search box. Select the database, and click Restore.
    Search for database to restore.png
  6. In the Restore Type, select Point in Time Restore
  7. In the Select Date and Time section, select the point in time to which you want to restore the databases. Databases will be restored to the last available committed transaction before the specified point in time. 
  8. Click Show databases to see all the databases that can be restored to the point in time. 
  9. Select the database(s) and click Restore.
  10. In the Restore Target page, select Restore to alternate SQL server instance.

    Restore to point in time - Restore to alternate SQL server instance.png
  11. In the Restore Settings page, perform the following tasks:

    Restore to point in time - Restore to alternate SQL server instance- Settings.png
    1. Select Server: Select the MS-SQL server to which you want to restore the database.
    2. Select Instance: Select the instance on the selected MS-SQL server to which you want to restore the database.
    3. Restore MDF, LDF, NDF files to different locations:  Select this checkbox to restore the MDF, LDF, and NDF files of the database to different locations on the selected server. If this option is selected, the option to browse or enter an absolute path to the MDF, LDF, and NDF file locations is enabled. Enter the absolute path to MDF, LDF, and NDF files or browse to the locations.
    4. Restore Location: Enter the absolute path to the restore location or browse to it. Database files are restored to the location you specify. This field disappears if you select the Restore MDF, LDF, NDF files to different locations checkbox.
    5. After Restore, leave databases in-
      1. 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.
      2. 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
      3. 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. If you select this mode, perform the following tasks:
        • Download Logs till: You can download the transaction logs between the time frame specified above this field. Select a date and time.
        • Restore Location: Enter an absolute path of the location where you want to restore additional transaction logs. You can also browse to that location.
  12. Click Finish. After the restore job completes, Phoenix leaves the database in the selected mode.

Restore to Availability Group

Use Phoenix to restore point in time databases to an AG. The restored databases are replicated across all the primary and secondary nodes in the AG.

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instances and availability groups.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. On the All SQL Resources page, click the standalone instance or availability group that hosts the database you want to restore.
  5. From the standalone instance or availability group details page, under the Configured Backup Sets section, select the backup set and click Restore, or
    You can also search for the database from the SQL Backup Sets page using the search box. Select the database, and click Restore.

    Search for database to restore.png
  6. In the Restore Type, select Point in Time Restore
  7. In the Select Date and Time section, select the point in time to which you want to restore the databases. Databases will be restored to the last available committed transaction before the specified point in time. 
  8. Click Show databases to see all the databases that can be restored to the point in time. 
  9. Select the database(s) and click Restore.
  10. In the Restore Target page, select Restore to Availability Group, and click Next.

    Restore to point in time- Restore to AG.png
  11. In the Restore Settings page, perform the following tasks:

    Restore to point in time - Restore to AG.png
    1. Availability Group: Select an AG to restore the database to. You cannot restore a database to an AG if an AG isn't configured, or the configured AG has an older client version.
    2. Restore MDF, LDF, NDF files to different locations: Select this checkbox to restore the MDF, LDF, and NDF files of the database to different locations on the availability group. If this option is selected, the option to browse or enter an absolute path to the MDF, LDF, and NDF file locations is enabled. Enter the absolute path to MDF, LDF, and NDF files or browse to the locations.
    3. Restore Location: In the Restore Location box, type or select the absolute path to the restore location. This option disappears if you enable the Restore MDF, LDF, NDF files at different locations check box.
      SQL Server 2016 and later versions support the Automatic Seeding feature for an AG. This feature allows you to automatically replicate the restored databases to all the nodes in the AG. If the automatic seeding feature is enabled in your setup, we recommend selecting the Restore with automatic seeding check box. Else, you can specify the shared network location to manually replicate the database across the nodes in the AG.
    4. Restore without automatic seeding: Select this option to automatically replicate the restored databases to all the nodes in the AG. When you select this check box, the Shared Network Location field disappears from the page. You can use this option for SQL Server 2016 and later versions. For SQL Server 2012 and SQL Server 2014, you must manually replicate the database to all the nodes in the AG by specifying the shared network location.
    5. Shared Network Location: Enter the absolute path to the network location to synchronize the database across the primary and secondary nodes in the AG. Ensure that this shared network location is accessible to all the nodes in the AG. The Shared Network Location field is mandatory in SQL Server 2012 and SQL Server 2014; however, it is optional in SQL Server 2016. This field disappears if the Restore with automatic seeding check box is enabled.
  12. 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. 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.

Point in time restore - available time range.png

Assume that your available time range is Nov 24, 2020 11:00:59 PM to Nov 27, 2020 10:49:51 AM, and you select Nov 25,2020, 12:00:00 AM as your point in time. Phoenix shows you all the available databases to the last available committed transaction before Nov 25, 2020, 12:00:00 AM for restore. 

After restore, you can choose to leave the databases in Standby mode. Phoenix allows you to download logs from the specified point-in-time till the last available committed transaction before Nov 27, 2020, 10:49:51 AM. If you select  Nov 26, 2020 12:00:00 AM, your database is restored to the transaction on Nov 25,2020, 12:00:00 AM, and you can download logs that you can use to restore your database upto  Nov 26, 2020 12:00:00 AM.

Restore to point in time- Standby mode.png

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: 2020-11-26 12-00-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 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 Nov 26,2020 12:00:00 AM.

Point-in-time restore workflow

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

Step Operation

1

You initiate a point-in-time restore.

2

 

Phoenix checks if the Phoenix agent is running. 

  • If the Phoenix agent is running, Phoenix runs the restore job.
  • If the Phoenix agent is not running, Phoenix queues the restore job. The restore job is completed after the Phoenix agent starts running. 

3

Phoenix validates the restore destination (Original, alternate, or availability group) and the location on the restore destination.

Note: Ensure that the location is not a drive. For example, C:\.  To restore a database, restore the database files to a sub-folder. For example, C:\DBFiles.

4 Phoenix validates if the SQL Server instance is available for restore. If the restore job replaces the original database, Phoenix also checks if the database is available for restore. 
6 Phoenix checks the free space available for restore and starts the restore job by identifying filesets for restore. Phoenix sequentially downloads filesets to the restore destination. Within a fileset (which might contain data belonging to more than one database), Phoenix simultaneously downloads files that belong to different databases. After the download of a fileset completes, Phoenix downloads the next fileset (which might also contain data that belongs to one or more databases) to the restore destination.
7 Phoenix uses the following syntax for the restore: <destination path>\<snapshot>\<Request ID>\<Fileset>\<Actual file>. The <Request ID> folder uniquely identifies each restore job.

8

The Phoenix agent picks up the transaction logs from the restore location, and:

  • Picks the user-specified date and time (or the selected marked transaction)
  • Checks which mode is enabled for restore:
    • If Recovery mode is enabled, Phoenix restores the database and leaves the database in the operational state (you cannot add more backup sets to it).
    • If No recovery mode is enabled, Phoenix restores the database and leaves the database in a pending state that cannot be accessed. 
    • If Standby mode is enabled, it identifies the transaction closest to the date and time specified. Phoenix restores the database changes up to the identified transaction using the transaction log. In addition, it downloads the log files containing the transactions after the point-in-time restore. 

If the original database is getting replaced, the Phoenix agent terminates all connections to the database and then restores it using the downloaded transaction logs. 

9 If the database is restored to an availability group, the Phoenix agent either restores the database to the specified shared network location and syncs the database to all the secondary nodes or uses the automatic seeding if available. 

After the restore completes: 

  • If Recovery mode is selected, the database appears as active for the instance that you specified. 
  • If No recovery mode is selected, the database appears as Restoring for the instance that you specified.
  • If Standby mode to allow restore of additional transaction logs is selected, the database appears as Standby/Read-Only for the instance that you specified. 
  • If Restore to Availability Group is selected, the database appears as synchronized on all the nodes of the AG.

Restoring the database to a marked transaction

Phoenix allows you to restore your database to a transaction you marked while committing a transaction.  The transaction mark restores let you restore your databases to a consistent state until a particular transaction updated your database. Transaction mark restores use log backups that are triggered incrementally after a full backup completes and a restore point exists.

Restore of an AG to the original server restores the database to the instance of the primary server. MS­-SQL server does not replicate marked transactions related to the transaction logs on the secondary nodes for AG databases. If you try to restore a transaction mark for an AG backup set from Phoenix, you may or may not see marked transactions that were created on the MS-SQL server, depending on whether the log backup happened from the primary or secondary node.

Procedure

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instances and availability groups.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. On the All SQL Resources page, click the standalone instance or availability group that hosts the database you want to restore.
  5. From the standalone instance or availability group details page, under the Configured Backup Sets section, select the backup set and click Restore, or
    You can also search for the database from the SQL Backup Sets page using the search box. Select the database, and click Restore.
  6. In the Restore Type, select Transaction Mark Restore.
    Transaction Mark restore.png
  7. In the Transaction Marks section, select the transaction mark.
  8. In the Databases section, select the databases that contain the transaction mark, and click Restore.
  9. You can either select Restore to original SQL server instance or an Restore to alternate SQL server instance.

    Restore to transaction mark - Restore target.png
  10. If you choose to Restore to original SQL server instance, enter the complete folder path of the location on the original server, where you want to restore the database in the Restore Location field.
    If you choose to Restore to alternate SQL server instance, enter the following details:

    Restore to transaction mark - alternate instance.png
    1. Restore Location: Enter the complete folder path of the location on the alternate SQL server.
    2. Select Server: Select the alternate server.
    3. Select Instance: Select the instance on the alternate server.
  11. In the Restore Option section, select one of the following:
    1. Before the selected transaction mark: Roll forward to the marked transaction, and exclude the marked transaction from the roll forward.
    2. At the selected transaction mark: Roll forward to the marked transaction, and include the marked transaction in the roll forward.
  12. Click Finish.

Note: Transaction mark restore can fail with SQL8 error if the transaction marks were recorded before logs were backed up.