PowerProtect Data Manager 19.15 Microsoft SQL Server User Guide

Centralized restore of a Microsoft SQL Server stand-alone database

You can perform a centralized restore of a full or transaction log backup of a Microsoft SQL Server stand-alone database in the PowerProtect Data Manager UI. The following procedure restores a single database.

1. In the PowerProtect Data Manager UI, select Restore > Assets and select the SQL tab.

   The Restore window displays all the databases that are available for restore.

   You can click or on the top right of the window to switch between the hierarchical (tree) view and list view. The hierarchical view uses a tree view to show the hierarchical relationships of the Microsoft SQL Server hosts, their application servers or instances, stand-alone database assets, and any Always On availability groups (AAGs) with their database assets. When you expand the hierarchical view, you can see all the assets and AAGs within a host and instance. When you select a host or instance container, all the contained assets and objects are also selected. You can also select individual assets or a group of assets within the host or instance container.

   In the list view, to filter the displayed list of assets if needed, you can click in the column heading Name, Status, Protection Policy, Host/Cluster/Group Name, Host Type, Application Name, Protection Type, Last Copy, or Network:

   • The Name column lists the Microsoft SQL Server database asset names.
   • The Status column lists the status as Available, Deleted, or Not Detected.
   • The Protection Policy column lists the names of the protection policies for the assets.
   • The Host/Cluster/Group Name column lists the hostnames.
   • The Host Type column lists the host types as AAG or Standalone.
   • The Application Name column lists the Microsoft SQL Server instance names.
   • The Protection Type column lists the Application Direct or VM Direct protection type for each asset.
   • The Last Copy column lists the dates and times of the backup copies within the specified date and time range.
   • The Network column lists the networks that are available under the selected host or cluster.

   NOTE:Only the assets that have backup copies are displayed. You can select assets only from the same Microsoft SQL Server host and instance.
2. Select the check box next to the stand-alone database, and click Restore.
   The restore wizard opens on the Copy Selection page, which provides a life cycle roadmap that displays all the backup copies available for each selected storage system.
3. On the Copy Selection page, select the backup copy that you want to restore, and then click Next.

   To filter the displayed list of backup copies if needed, you can click in the column heading Copy Type, Create Time, Copy Status, Location, or Storage Unit:

   • The Copy Type column lists the backup copy types as Full or Log.
   • The Create Time column lists the dates and times when the backup copies were created.
   • The Copy Status column lists the backup copy status as Available, Deleting, Deletion Failed, Deletion Failed (Agent Catalog), Cloud Tiering, Cloud Recalling, Replicating, Restoring, or Ready for Cloud Tiering.
   • The Location column lists the backup copy locations as Local or Local_Recalled.
   • The Storage Unit column lists the storage units of the backup copies.
4. On the Scope page, select one of the following options, and then click Next:
   • Restore to Microsoft SQL Server Instance—Restores the database backup to a single server instance.

   • Restore to Always On Availability Group—Restores the database backup to a database in an AAG.

     During the restore, the database is removed from the AAG. After the restore, the database is added back to the AAG.

5. On the Location page, select one of the following options, and then click Next:

   • Restore to Original—Restores and overwrites the selected database.

     NOTE:If you selected to restore the database to an AAG, the Restore to Original option is unavailable. Stand-alone databases cannot be restored to the original AAG location.

   • Restore to Alternate—Restores the selected database to an alternate database or restores the selected database as a new database on the same instance or a different instance.

     Click or above the table to see the list of available databases. In the hierarchical view, you must expand the list to see all the databases. The hierarchical view displays the databases for each instance on each available Microsoft SQL Server host. A refresh icon next to each hostname enables you to request the rediscovery of all instances and databases on a host:

     • To restore to an alternate database, select the alternate database from the database list.

     • To restore as a new database on the same instance or a different instance, click Restore as new database next to the required instance name. In the Restore as new database dialog, type the name of the new database in the Database Name field.

       NOTE:

       The database name must have 128 or fewer characters. Do not specify a Microsoft SQL Server system database name such as master, model, msdb, or tempdb.

       After the new database name is added to the database list, you can optionally delete the name from the list, if needed, by clicking Remove next to the name.

6. On the Select File Location page, select one of the following options for Restore database files to, and then click Next:

   • Original file location (location at backup time)

     NOTE:If the directory path cannot be created during the centralized restore, the restore fails.
   • Default file location as set by Microsoft SQL Server

   • User-specified file location

     NOTE:When you select this option, you must specify the restore file directories for the database files and log files.
7. On the Options page, select any the following options, and then click Next:

   • Overwrite Databases WITH REPLACE—Overwrites the existing databases during the restore operation.

     NOTE:

     When a database exists but the Overwrite Databases WITH REPLACE option is not selected, the restore fails.

     After you perform a system database rebuild in the Microsoft SQL Server, you must specify the WITH REPLACE option when you restore the system databases, including master, model, and msdb.

   • Tail Log—Performs a tail-log backup.

     NOTE:The tail-log backup option is displayed when the destination is the same as the host. This option is selected by default.

   • Restore State—Select one of the following options:

     • RESTORE WITH RECOVERY—Leaves the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.
     • RESTORE WITH NO RECOVERY—Leaves the database nonoperational and does not roll back the uncommitted transactions. Additional transaction logs can be restored.
   • Troubleshooting mode—To enable troubleshooting logging, click the toggle button to change the setting to Enabled. Then the Log level option field appears.
   • Log level—If you enabled troubleshooting mode, select the preferred debug log level:
     • Info—Includes information such as status changes. This is the default log level for scheduled backups and restores.
     • Debug—Additional information that helps with problem diagnosis.
     • Trace—The most detailed amount of information for complex problem diagnosis.
8. On the Summary page:
   1. Review the Select Copy, Scope, Location, File Location, and Options information to ensure that the restore details are correct.
      NOTE:When the specified database name matches the name of an existing database, the restore overwrites the existing database.
   2. Click Restore.
      The restore operation starts. Then the Go to Jobs informational dialog box appears with a link to the Jobs page where you can monitor the restore job.