Skip to main content
Druva Documentation

Restore SQL server databases using snapshots

Snapshots

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:

Hot snapshot restore

A restore of hot snapshots 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 Phoenix CloudCache. Such a restore operation continues until the server data is restored to the location that you specified.

To locate hot snapshots, expand Hot on the Restore Data window. 

SQL_Hot_SnapshotRestore.PNG

Warm snapshot restore

Warm snapshots restores are restores of point-in-time images of backup data dating back to 90 days in time or the duration you specify under keep all snapshots setting (whichever is greater). Restores of warm snapshots continue until the server data is restored to the location that you specified. 

To locate warm snapshots, expand Warm on the Restore Data window. 

SQL_Warm_SnapshotRestore.PNG

Cold snapshot restore

Restores of cold snapshots are on-request restores of server data dating back to more than 90 days in time or the duration you specify under keep all snapshots setting (whichever is greater). Such data, known as the cold snapshots, is stored in the cold storage. In the event of loss or corruption of this data, you can perform a restore by using the cold snapshots. To start a restore of cold snapshots, you must first initiate a defreeze operation, during which Phoenix retrieves data from the cold storage. This retrieved data is considered as thawed and is available for restore. After the defreeze operation completes, Phoenix sends an email informing you about the availability of the data, and the duration for which your data remains available.

To locate cold snapshots, expand Cold on the Restore Data window.

SQL_Cold_Snapshot_Restore.PNG

To locate thawed snapshots, expand Thawed on the Restore Data window. 

SQL_restore_thawed.png

(Cold snapshots) To defreeze your data

To start a cold snapshot restore, initiate a defreeze operation of the cold snapshots. 

  1. Log in to the Phoenix Management Console
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers. 
  4. On the SQL Backup Sets tab, click the instance or the AG you want to restore.  
  5. On the MS-SQL page, click Restore > Snapshot Restore
  6. In the left pane, expand Cold, and click the snapshot that you want to defreeze. 
  7. Under Home , click Defreeze.

The defreeze operation is completed in 4 hours. After completion, Phoenix sends an email informing you that your data is thawed. This data stays thawed for the duration specified in the email. You must perform a restore of your thawed data within this duration following instructions in the server. 

Restore the master database

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers. 
  4. In the SQL Backup Sets tab, click the instance you want to restore. 
  5. On the MS-SQL page, click Restore > Snapshot Restore. The timestamps of the snapshots are displayed according to the server time zone. For example, the timestamps for servers located in New York and London are displayed according to EST and UTC time zones, respectively.
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore.
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore.
  6. On the Restore Data page, in the Instances section, click an instance.
  7. In the Databases section, select the master database, and click Restore.
    master_db_restore1.png
    Phoenix displays an error if you select a combination of databases along with the master database.
  8. In the Restore Database Files section, from the Select server list select the MS-SQL server instance or AG to which you want to restore the master database. Servers that you configured as MS-SQL servers, along with the original server, appear in this list.
  9. In the Restore Database Files section, provide the following details: 
    master_db_restore.png
    • Select server: From the Select server list, select the MS-SQL server to which you want to restore the database.
    • Restore MDF, LDF, NDF files at different locations:  Enable this check box to restore the MDF, LDF, and NDF files at different locations. The fields that let you type or select the absolute path to the restore location appear when you enable this check box.
    • Restore location: In the Restore location box, type or select the absolute path to the restore location. This field disappears if you enable the Restore MDF, LDF, NDF files at different locations check box. 

    Symbolic links and mounted folders do not appear in the folder structure. If the server is not connected to the Phoenix Cloud, 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 Phoenix Cloud.

    Click Finish.
  10. Use the database_files.txt file to determine the database mapping. 
  11. Stop the instance to which you want to attach the master database.
  12. 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
  13. Start the instance again. 

Restore the databases to the original instance

When you restore a database to its original location, you can restore the database 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. 

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

Restore the database as a copy

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. Under the All SQL Resources tab, click the instance or availability group that hosts the backup set that you want to restore. 
  5. On the server page, click Restore > Snapshot Restore on the backup set that you want to restore. The Restore dialog appears. 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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of the backup set. 
  6.  On the left pane, select one of the snapshots:
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore. 
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore. 
  7. On the right pane, select an instance, the databases in the instance, and then click Restore.  
  8. On the Restore from snapshot page, click Restore to original SQL server instance and click Next.
  9. On the Restore to original SQL server section:
    • Select Restore as a copy and provide the following information:
      • Restore MDF, LDF, NDF files at different locations:  Enable this check box to restore the MDF, LDF, and NDF files at different locations. The fields that let you type or select the absolute path to the restore location appear when you enable this check box.
      • 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.
      • Restore Location: In the Restore Location box, type or select the absolute path to the restore location. The database files are restored to the location you specify. This field disappears if you enable the Restore MDF, LDF, NDF files at different locations check box. 
    • Under After restore, leave databases in, select one of the following:
      • 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
      • Standby mode: 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, provide the full folder path of the location where you want to restore additional transaction logs in the Specify Location box. 
    After the restore job completes, Phoenix leaves the database in the selected mode.
  10. Click Finish.

Replace the original database

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. Under the All SQL Resources tab, click the instance or availability group that hosts the backup set you want to restore. 
  5. On the server page, click Restore > Snapshot Restore on the backup set that you want to restore. The Restore dialog appears. 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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of the backup set. 
  6.  On the left pane, select one of the snapshots:
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore. 
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore. 
  7. On the right pane, select an instance, the databases in the instance, and then click Restore.  
  8. On the Restore from snapshot page, click Restore to original SQL server instance and click Next.
  9. In the Restore to original SQL server section:
    • Select Replace original database.
    • Under After restore, leave databases in, select one of the following:
      • 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
      • Standby mode: 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, provide the full folder path of the location where you want to restore additional transaction logs in the Specify Location box. 
    After the restore job completes, Phoenix leaves the database in the selected mode.
  10. Click Finish.

Note: Symbolic links and mounted folders do not appear in the folder structure. If the server is not connected to Phoenix Cloud, 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 Phoenix Cloud.

Restore the database to an alternate MS-SQL server instance

  1. Log in to the Phoenix Management Console.
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers.
  4. Under the All SQL Resources tab, click the instance or availability group that hosts the backup set that you want to restore. 
  5. On the server page, click Restore > Snapshot Restore on the backup set that you want to restore. The Restore dialog appears. 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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of the backup set. 
  6.  On the left pane, select one of the snapshots:
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore. 
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore. 
  7. On the right pane, select an instance, the databases in the instance, and then click Restore.  
  8. On the Restore from snapshot page, click Restore to alternate SQL server instance and click Next.
  9. In the Restore to alternate SQL Server Instance section, provide the following details:
    • Restore MDF, LDF, NDF files at different locations:  Enable this check box to restore the MDF, LDF, and NDF files at different locations. The fields that let you type or select the absolute path to the restore location appear when you enable this check box.
    • 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.
    • 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 you enable the Restore MDF, LDF, NDF files at different locations check box. 
    • Under After restore, leave databases in, select one of the following:
      • 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
      • Standby mode: 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, provide the full folder path of the location where you want to restore additional transaction logs in the Specify Location box. 
      After the restore job completes, Phoenix leaves the database in the selected mode. Phoenix disables the No recovery and Standby modes for the Phoenix clients older than version 4.7.6.
  10. Click Finish

Restore the database to Availability Group

Use Phoenix to add the restored database(s) to an AG. The databases are restored and replicated across all the primary and secondary nodes in the AG. To use this feature, upgrade the Phoenix agent to version 4.7.6 or later. The database can be added to an AG only if the file structure in all the nodes of the AG is same. For example, a driver present on one node must be present on the other nodes of the AG.

  1. Log in to the Phoenix Management Console. 
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers. 
  4. Under the All SQL Resources tab, click the instance or availability group that hosts the backup set that you want to restore. 
  5. On the server page, click Restore > Snapshot Restore on the backup set that you want to restore. The Restore dialog appears. 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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of the backup set. 
  6. On the left pane, select one of the snapshots:
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore. 
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore. 
  7. On the right pane, select an instance, the databases in the instance, and then click Restore.  
  8. In the next section, select Restore to Availability Group and click Next
  9. In the Restore to availability group section, enter the following details:
    • Restore MDF, LDF, NDF files at different locations:  Enable this check box to restore the MDF, LDF, and NDF files at different locations. The fields that let you type or select the absolute path to the restore location appear when you enable this check box.
    • 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. 
    • 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 you to select 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.
    • Restore with automatic seeding: Enable the Restore with automatic seeding check box 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.
    • 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 this shared network location is accessible to all the nodes in the AG. The Shared Network Location field is a mandatory field in SQL Server 2012 and SQL Server 2014, however, it is an optional field in SQL Server 2016. This field disappears if the Restore with automatic seeding check box is enabled. 
  10. Click Finish.

Restore database files

  1. Log in to the Phoenix Management Console. 
  2. On the menu bar, click All Organizations, and select the required organization from the drop-down list.
  3. On the menu bar, click Protect > MS-SQL Servers. 
  4. Under the All SQL Resources tab, click the instance or availability group that hosts the backup set that you want to restore. 
  5. On the server page, click Restore > Snapshot Restore on the backup set that you want to restore. The Restore dialog appears. 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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of the backup set. 
  6. Under the left pane, select one of the snapshots:
    • Hot snapshots: In the left pane, expand Hot, and click the snapshot that you want to restore. 
    • Warm snapshots: In the left pane, expand Warm, and click the snapshot that you want to restore. 
    • Thawed snapshots: In the left pane, expand Thawed, and click the snapshot that you want to restore. 
  7. Select the user or the system databases and click Restore
  8. On the Restore from snapshot page, click Restore database files and click Next
  9. In the Restore Database Files section, provide the following details:
    • Restore MDF, LDF, NDF files at different locations:  Enable this check box to restore the MDF, LDF, and NDF files at different locations. The fields that let you type or select the absolute path to the restore location appear when you enable this check box.
    • From the Select server list, click the instance to which you want to restore the database. Servers that you configured as MS-SQL servers, along with the original server, appear in this list. 
    • 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
  10. Click Finish.

Note: Symbolic links and mounted folders do not appear in the folder structure. If the server is not connected to the Phoenix Cloud, 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 Phoenix Cloud.

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 this article in the Microsoft library.

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

Phoenix checks if the Phoenix agent is running. 

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

4

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 Phoenix validates if the instance to which restore is initiated is available. 
6 Phoenix validates if the databases for restore are available. 
7 Phoenix checks the free space available at the restore destination. 

8

Phoenix starts the restore operation 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 performs a simultaneous download of files belonging to different databases. After the download of a fileset completes, Phoenix downloads the next fileset (which might also contain data belonging to one or more databases) to the restore destination.

9

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, 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, Phoenix restores the databases as rst_<Database Name>. However, if 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 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, Phoenix repeats Step 9 on all the secondary nodes of the AG. 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.