Skip to main content

How can we help you?

Druva Documentation

Restore SQL server databases using snapshots

Snapshots

Druva Phoenix supports three types of restores for MS-SQL server:

  • Snapshot Restore
  • Point in Time Restore
  • Transaction Mark

Restore types.png

Point in Time restores and Transaction Mark restores are performed using MS-SQL transaction logs. In this article, we discuss Snapshot Restores. Druva Phoenix allows you to restore from Hot, Warm and Cold snapshots of MS-SQL server.

To restore the databases, you select a snapshot or a restore point. When you trigger a restore job, you can see that the snapshots are categorized as:

Note: The account used for SQL backups and restores must also have the CREATE DATABASE permission for successful restores from snapshots. If the account used for SQL backups and restores does not have the CREATE DATABASE permission, the restores may fail with the SQL7 error. For more information, see GRANT Database Permissions

Hot snapshot restore

A restore from a hot snapshot is an on-demand restore of server data that resides on Phoenix CloudCache. Hot snapshots are point-in-time images of backup data stored on the Phoenix CloudCache. Such a restore operation continues until the server data is restored to the location that you specified.

In the navigation pane on the left, click All SQL Resources. In the right pane, click the instance or availability group whose Hot snapshot you want to restore. In the instance or availability group details page, under the Configured Backup Sets section, select the backup set, and click Restore. The Snapshot Viewer displays the date and timestamp of the latest snapshot. Click the drop-down next to it, click the Hot tab, and then select the snapshot you want to restore.

Hot Snapshot.png

Warm snapshot restore

Warm snapshots are point-in-time copies of backup data of the last 15 days. These snapshots are stored in the Amazon S3 storage. Data from warm snapshots can be restored immediately.   

In the navigation pane on the left, click All SQL Resources. In the right pane, click the instance or availability group whose Warm snapshot you want to restore. In the instance or availability group details page, under the Configured Backup Sets section, select the backup set, and click Restore. The Snapshot Viewer displays the date and timestamp of the latest snapshot. Click the drop-down next to it, click the Warm tab, and then select the snapshot you want to restore.

Warm snapshot.png

Cold snapshot restore (Applicable to LTR customers)

Cold snapshots are point-in-time copies of backup data older than 15 days. These snapshots are stored in the Amazon Glacier Deep Archive. At the time of restore, data from the cold tier is retrieved, moved temporarily to the warm tier, and then restored. Once you click Restore and initiate the restore, the data retrieval from cold tier and its restore from the warm tier happens automatically. Warmed up data is deleted from the warm tier after 10 days.
In the navigation pane on the left, click All SQL Resources. In the right pane, click the instance or availability group whose Cold snapshot you want to restore. In the instance or availability group details page, under the Configured Backup Sets section, select the backup set, and click Restore. The Snapshot Viewer displays the date and timestamp of the latest snapshot. Click the drop-down next to it, click the Cold tab, and then select the snapshot you want to restore.

Cold Snapshot restore.png

To restore your cold-tier data

To restore data from the cold tier, perform the following tasks:

  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 availability group.
  3. On the menu bar, click Protect > MS-SQL server.
  4. On the All SQL Resources page, click the standalone instance or availability group whose cold-tier data 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. You can also search for a resource, backup set, or database by using the search box in the SQL Backup Sets page, select the backup set, and click Restore.

    Search for databases.png
  6. In the Restore Data dialog box, under Restore Type, select Snapshot Restore.

  7. Click the drop-down next to the date and timestamp, and select the Cold tab. Select the database(s) in the selected snapshot, and click Restore.

    Cold Snapshot restore.png
 

Restore the master database using Restore database files

  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 whose master 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 master database from the SQL Backup Sets page using the search box. Select the database, and click Restore.

    Search for databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.

  7. Click the drop-down next to the date and timestamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. From the list of databases, select the master database.

  2. Click Restore.

    Restore master database.png

Note: Druva Phoenix displays an error if you select other databases along with the master database.
Master database selected with others.png

  1. In the Restore Target screen, select Restore database files.  

    Restore from snapshot - Restore database files (master).png
  2. In the Restore Settings screen, under Restore Database Files, perform the following tasks:

    Restore the master database using Restore database files_11.png
    1. In the Select Server drop-down list, select an MS-SQL server instance or availability group to which you want to restore the master database. Servers that you configured as MS-SQL servers, along with the original server,are available for selection in this list. 
    2. Select the Restore MDF, LDF, NDF files to different locations checkbox to restore the MDF, LDF, and NDF files of the master database to different locations on the selected server. If this option is selected, the option to browse to 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 field, type or select the absolute path to the restore location. This field disappears if you enable the Restore MDF, LDF, NDF files to different locations check box.

      If there is not enough available free space at the selected restore location, then a warning message is displayed to the user that without the required free space, the restore operation will fail.
      warning.png

If the server is not connected to the Druva Cloud, Druva Phoenix cannot fetch the folder structure data. To initiate a restore, you must type the absolute path to the restore destination. The restore operation remains in a pending state, and starts only after the server connects to the Druva Cloud.

  1. Click Finish.
  2. Use the database_files.txt file to determine the database mapping. See Phoenix agent metadata for more information on database_files.txt.
  3. Stop the instance to which you want to attach the master database.
  4. Attach the restored master database files to a database within the instance. For a full set of instructions, see Moving the master database in Move System Databases
  5. Start the instance again. 

Restore to original SQL server instance

When you restore a database on a standalone instance to its original SQL server instance, 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, Druva 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 SQL server instance, Druva 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 databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.
  7. Click the drop-down next to the date and time stamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. Select the database to be restored, and then click Restore.

  2. In the Restore Target page, select Restore to original SQL server instance, and click Next.

    Restore from snapshot - Restore to original instance.png
  3. In the Restore Settings page, select Restore as a copy and perform the following tasks:

    Restore to original SQL server instance_10_1.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.

      If there is not enough available free space at the selected restore location, then a warning message is displayed to the user that without the required free space, the restore operation will fail.
      warning.png
    3. Under After restore, leave databases in, select one of the following:
      1. Recovery mode: In this mode, Druva Phoenix rolls back all the uncommitted transactions and keeps the database in the ready state. Druva 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, Druva 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: In this mode, Druva 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, provide the absolute folder path of the location where you want to restore additional transaction logs in the Specify Location field.
  4. Click Finish. After the restore job completes, Druva 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 databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.
  7. Click the drop-down next to the date and time stamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. Select the database to be restored, and then click Restore.
  2. In the Restore Target page, select Restore to original SQL server instance, and click Next
  3. In the Restore Settings page, select Replace original database.

    Restore original database - Replace original database.png
  4. Under After restore, leave databases in, select one of the following:
    1. Recovery mode: In this mode, Druva Phoenix rolls back all the uncommitted transactions and keeps the database in the ready state. Druva 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, Druva 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: In this mode, Druva 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, provide the full folder path of the location where you want to restore additional transaction logs in the Specify Location field.
  5. Click Finish. After the restore job completes, Druva Phoenix leaves the database in the selected mode.

Note: If the server is not connected to Druva Cloud, Druva Phoenix cannot fetch the folder structure data. To initiate a restore, you must type the absolute path to the restore destination. The restore operation remains in a pending state, and starts only after the server connects to the Druva Cloud.

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 databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.
  7. Click the drop-down next to the date and time stamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. Select the database to be restored, and then click Restore.
  2. In the Restore Target page, select Restore to alternate SQL server instance, and click Next.

    Restore from snapshot - Restore to alternate instance.png
  3. In the Restore Settings page, perform the following tasks:

    Restore to alternate SQL server instance_10.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 at 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.

      If there is not enough available free space at the selected restore location, then a warning message is displayed to the user that without the required free space, the restore operation will fail.
      warning.png
    5. After Restore, leave databases in-
      1. Recovery mode: In this mode, Druva Phoenix rolls back all the uncommitted transactions and keeps the database in the ready state. Druva 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, Druva 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: In this mode, Druva 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, provide the full folder path of the location where you want to restore additional transaction logs in the Specify Location field.
  4. Click Finish. After the restore job completes, Druva Phoenix leaves the database in the selected mode. Druva Phoenix disables the No recovery and Standby modes for the Phoenix agents older than version 4.7.6.

Restore to Availability Group

Databases restored to an availability group are replicated across all the primary and secondary nodes in the AG. A database can be restored to an AG only if the file structure across all nodes of the AG is the same.

  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 databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.
  7. Click the drop-down next to the date and time stamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. Select the database to be restored, and then click Restore.
  2. In the Restore Target page, select Restore to Availability Group, and click Next.

    Restore from snapshot - Restore to AG.png
  3. In the Restore Settings page, perform the following tasks:

    Restore to Availability Group_10.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

      If there is not enough available free space at the selected restore location, then a confirmation message is displayed to the user that without the required free space, the restore operation will fail.

      warning.png
      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 with 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.
  4. Click Finish.

Restore database files

This is a Limited Availability draft of the documentation. This documentation is not final, and it will get updated until the General Availability of the feature.

  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 whose database files 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 databases.png
  6. In the Restore Type, ensure that Snapshot Restore is selected.
  7. Click the drop-down next to the date and timestamp, and select a Hot, Warm or Cold snapshot.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during the creation of the backup set.

  1. From the list of databases, select the user or system databases.
  2. Click Restore.
  3. In the Restore Target screen, select Restore database files and click Next.

    Restore from snapshot - Restore database files.png
  4. In the Restore Settings screen, under Restore Database Files, perform the following tasks:

    Restore database files - Restore Settings.png
    1. Select Server: Select an MS-SQL server instance or availability group to which you want to restore the databases. Servers that you configured as MS-SQL servers, along with the original server, are available for selection in this list. If the restore point has a VDI differential snapshot, then this server list only contains the servers that are on SQL agent version 4.9.4-110537 or later.
    2. Restore MDF, LDF, NDF files at different locations: Select this checkbox to restore the MDF, LDF, and NDF files of the selected databases to different locations on the selected server. If this option is selected, the option to browse to 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.

If the server is not connected to the Druva Cloud, Druva Phoenix cannot fetch the folder structure data. To initiate a restore, you must type the absolute path to the restore destination. The restore operation remains in a pending state, and starts only after the server connects to the Druva Cloud.

  1. Restore Location: In the Restore Location box, type or select the absolute path to the restore location. Database files are restored to the location you specify. This field disappears if the Restore MDF, LDF, NDF files at different locations check box is enabled.

12. Click Finish.

After restoring the database files:

  1. Copy the system database files to C:\Program Files\Microsoft SQL Server\<Instance Name>\MSSQL\DATA.

  2. Use the database_files.txt file to determine the database mapping. Attach the restored databases to an instance. For a full set of instructions, see Attach a Database.

MS-SQL server database snapshot restore workflow

The following steps describe the restore job when you trigger a snapshot restore of your databases of the instances or AG.

Step Operation

1

You or another administrator initiates a restore. 

2

Druva Phoenix checks if the Phoenix agent is running. 

  • If the agent is running, Druva Phoenix executes the restore operation.
  • If the agent is not running, Druva Phoenix queues the restore request. The request is executed after the Phoenix agent on the MS-SQL server starts running.
3 Druva Phoenix validates the restore destination (original instance or another MS-SQL server). 

4

Druva Phoenix validates if the restore destination is not a drive. For example, a restore to D:\ fails. 

Note: Set a restore location to a subfolder, and not the drive, for example, D:\ThisFolder.  

5 Druva Phoenix validates if the instance to which restore is initiated is available. 
6 Druva Phoenix validates if the databases for restore are available. 
7 Druva Phoenix checks the free space available at the restore destination. 

8

Druva Phoenix starts the restore operation by identifying filesets for restore. Druva Phoenix sequentially downloads filesets to the restore destination. Within a fileset (which might contain data belonging to more than one database), Druva Phoenix performs a simultaneous download of files belonging to different databases. After the download of a fileset completes, Druva Phoenix downloads the next fileset (which might also contain data belonging to one or more databases) to the restore destination.

9

Druva Phoenix uses the following syntax for the restore: <destination path>\<snapshot>\<Request ID>\<Fileset>\<Actual file>. The <Request ID> folder uniquely identifies each restore request.

10

Additionally, Druva Phoenix also creates the database_files.txt file at D:\restore\<snapshot>\<Request ID>. This file contains details of how the database files are mapped to the database, and how the database is mapped to its instance in a Unicode format. 

11

At the location that you specified, Druva Phoenix restores the databases as rst_<Database Name>. However, if Druva Phoenix finds that rst_<Database Name> is already present at the location, it appends an incremental counter to the database name. For example, at the time of restoring database DB, if Druva Phoenix finds rst_DB from a previous restore operation, the database DB is restored as rst_DB_1. The counter increments by 1 for every occurrence of an existing restore dataset.
12 In case of restore to AG, Druva Phoenix repeats Step 9 on all the secondary nodes of the AG. Druva Phoenix backs up the database to the shared network location provided by you and the restored database is replicated on all the primary and secondary nodes of the AG. 

13

After the restore completes,

  1. If "Recovery mode" is selected, the database becomes active for the instance that you specified. 
  2. If "No recovery mode" is selected, the database appears as "Restoring" for the instance that you specified.
  3. 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. 
  4. If "Restore to Availability Group" is selected, the database appears as synchronized on all the nodes of the AG.