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

Note: 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. 

To 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.
    • 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.

      Note: 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.

  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.

    Note: Phoenix displays an error if you select a combination of databases along with the master database.

    Restore_master_DB.PNG
  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.

    Note: Servers that you configured as MS-SQL servers, along with the original server, appear in this list.

  9. In the Specify location box, type or select the absolute path to the restore location, and then click Finish
    Restore_master_restore_db_files.PNG

    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.

  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. 

To restore user databases and other system databases

  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, select the MS-SQL server that you want to restore. 
  5. Click Restore. The Restore Data page appears.
  6. In the left pane, expand Hot, and click the snapshot that you want to restore. 
    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 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.
  7. In the Instances section, click an instance. The Databases section displays the list of databases.
  8. Select the user or the system databases and click Restore.  
  9. On the Restore from snapshot page, click Restore database files and click Next
    Restore_database_files_AG.PNG
  10. In the Restore Database Files section, from the Select server list, click the instance to which you want to restore the database.
    Note: Servers that you configured as MS-SQL servers, along with the original server, appear in this list. 
  11. In the Specify location box, type or select the absolute path to the restore location, and click Finish.
    SQL_Restore_Database_Files_userDB_server_path.PNG

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.

  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.

Restore the databases to the original instance

Note : Restore to original in case of AG backup set restores the database to the instance of the primary server.

  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 SQL Backup Sets tab,  select the MS-SQL server that you want to restore. 
  5. Click Restore
  6. Click the MS-SQL server that you want to restore, and then click Restore.  The Restore Data page appears.
    • 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. 
      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 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.
  7. In the Instances section, click an instance. The Databases section displays the list of databases.
  8. Select the user or the system databases and click Restore.  
  9. On the Restore from snapshot page, click Restore to original SQL server instance and click Next.
    Restore_snapshot_original_server_instance.PNG
  10. In the Restore Location box, type or select the absolute path to the restore location. 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
    • 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.
  11. Click the respective mode:
    • Recovery mode: Click Recovery mode and click Finish.
    • No recovery mode: Click No recovery mode and click Finish.
    • Standby mode: Click Standby mode to allow restore of additional transaction logs. In the Specify location box, type or select the absolute path to the restore location, and 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 SQL Backup Sets tab,  select the instance or AG that you want to restore. 
  5. Click Restore
  6. Click the MS-SQL server that you want to restore, and then click Restore.  The Restore Data page appears.
    • 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. 

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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of backup set.

  1. On the Restore Data page, in the Instances section, click an instance. The Databases section displays the list of databases.
  2. Select the user or the system databases and click Restore. The Restore from snapshot page opens.
  3. Click Restore to alternate SQL server instance and click Next
    Restore_alternate_SQL_server_instance.PNG
  4. In the Restore to alternate SQL Server Instance section, select the server to restore the database to, select the instance, and specify the location of restore.
  5. 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 for restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed.
    • 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.

    Note: Phoenix disables the No recovery and Standby modes for the Phoenix clients older than version 4.7.6.

  1. Click the respective mode:
    • Recovery mode: Click Recovery mode and click Finish.
    • No recovery mode: Click No recovery mode and click Finish.
    • Standby mode: Click Standby mode to allow restore of additional transaction logs. In the Specify location box, type or select the absolute path to the restore location, and click Finish.

Restore the database to Availability Group

You can add the restored database(s) to an AG using Phoenix. 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.

Note: The database can be added to an AG only if the file structure in all the nodes of the AG is same. For example, there should not be a driver present in one node that is not 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. On the SQL Backup Sets tab,  select the instance or AG that you want to restore. 
  5. Click Restore
  6. Click the MS-SQL server that you want to restore, and then click Restore. The Restore Data page appears.
  • 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. 

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 time zones, respectively. The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of backup sets.

  1. On the Restore Data page, in the Instances section, click an instance. The Databases section displays the list of databases.
  2. Select the databases and click Restore. The Restore from snapshot page opens.
  3. Click Restore to Availability Group and click Next.  
  4. In the Select restore location section, enter the details in the fields as shown in the following screen shot:
    Restore_AG_Snapshot.PNG

     
    1. 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. 
    2. Restore Location: In the Restore Location box, type or select the absolute path to the restore location.

      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.
       
    3. Restore with automatic seeding: Select 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.
      Restore_with_automatic_seeding.PNG

      Note: You can select this check box only 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.

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

      Note: 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.

    5. Click Finish.

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.