You are here:

Create a Restore Job Definition - SQL

ECX leverages Copy Data Management technology for recovering application databases through Database Restore jobs. Your SQL clones can be utilized and consumed instantly through ECX Instant Disk Restore jobs. ECX catalogs and tracks all cloned instances. Instant Disk Restore leverages iSCSI or fibre channel protocols to provide immediate mount of LUNs without transferring data. Snapshotted databases are cataloged and instantly recoverable with no physical transfer of data. Point-in-time recovery is supported with log forwarding.

The following Microsoft SQL Server workflows are supported:

Instant Database Restore - Provides instant recovery using a non-masked image and point-in-time transaction logs.

When performing a restore to a primary replica of a specific AlwaysOn Availability Group, the restore database is added to the destination availability group, however, the secondary replica is not automatically replicated.

When performing a restore to a secondary replica, the database is restored with the "norecovery" option, and the database is set to a "restoring" state. If managing transaction log backups without using ECX, you can manually restore log files, and add the database to an availability group, assuming the lsn of the secondary and primary database copies meet the criteria.

Instant Disk Restore - Mounts a database for restores with application and operating system support. An Instant Disk Restore of an AlwaysOn database is restored to the local destination instance.

Instant Seeding - Instant Seeding is used when a primary database is up and running, a secondary replica exists (but no secondary database). The primary database can be protected through an ECX Copy job, with log backups performed regularly. The seeding process restores the database to the copy time and applies the transaction logs of the primary database. The database is then added to an availability group at the end of the restore.

Instant Seeding is a recovery template available for SQL AlwaysOn Availability Groups that initializes secondary replica instances using storage copies. During an Instant Seeding restore, snapshot data along with log backups are restored to the secondary database, then added to the AlwaysOn Availability Group. Other AlwaysOn Availability Group restore jobs place databases in "restoring" states or only create local instances. Additional processes at the end of the Instant Seeding restore job add the database of a secondary replica to the Availability Group.

When creating the Instant Seeding job definition, primary and secondary replicas of the original Availability Group are displayed as valid restore destinations. All log backups after the copy job time are restored; no point-in-time options are required. For more information, see the Instant Seeding Prerequisites and Limitations section of the Microsoft SQL Server Requirements.

BEFORE YOU BEGIN:

Create and run a SQL Backup job. See Create a Backup Job Definition - SQL.
Review SQL requirements. See Microsoft SQL Server Requirements and Microsoft SQL Server Support FAQ.
For email notifications, at least one SMTP server must be configured. Before defining a job, add SMTP resources. See Register a Provider.
You must add credentials to the destination virtual machine when recovering with the subnet option. See Add Credentials to a Virtual Machine.

MICROSOFT SQL SERVER CONSIDERATIONS:

You must use the Overwrite existing database option to restore a Microsoft SQL database to a source instance. Note that in an Instant Disk Restore or Instant Database Restore job, the restore destination cannot be the source instance if this option is not selected. Due to Microsoft SQL restrictions, two databases with the same ID cannot exist on the same instance, including if the restored database is renamed through the job definition.
You must use a proxy node when performing a restore to a SQL Failover cluster. Windows requires signatures to be unique. When a disk is attached that has a signature equal to one that is already attached, Windows keeps the disk in “offline” mode and does not read its partition table or mount its volumes. To prevent the issue of disk signature collision, during Instant Database Restore, ECX leverages a Windows proxy server to temporarily mount disks from snapshots, generate a new signature, then mount to original server.
You must manually create a directory to store VSS provider logs when running ECX 2.6 and earlier. Create the following directory structure on the SQL server: c:\temp\ecx\logs
Note that Instant Disk Restores of AlwaysOn databases are restored to local instances only. Instant Database Recoveries of AlwaysOn databases are joined to destination Availability Groups if the restore target is a primary replica, and restored to the local instance in a "RESTORING" state if the restore target is a secondary replica.
When running SQL Server 2012, certain Availability Group properties must be modified in order for log backups to generate in their specified location. Under Backup Preferences, set the Where should backups occur? option to Any Replica. Under Replica backup priorities, set the primary server instance to 55 and the secondary server instance to 45.
It is recommended to keep the SQL transaction log location on a different disk than the SQL database data and log files.
When creating an Instant Seeding restore job definition, the destination must be a non-system drive. The SQL database and log files must be located on non-system drives.

Best Practice: Create a schedule before creating a job definition so that you can easily add the schedule to the job definition.

To create a Microsoft SQL Restore job definition:

Click the Jobs tab. Expand the Database folder, then select SQL.
Click New , then select Restore. The job editor opens.
Enter a name for your job definition and a meaningful description.
Select Microsoft SQL (Standalone and Failover Cluster) or Microsoft SQL (Always On Availability Group) .
Select a template. Available options include Instant Database Restore for Microsoft SQL Standalone/Failover Cluster and Always On Availability Group jobs, Instant Disk Restore , and Instant Seeding for Microsoft SQL Always On Availability Group jobs.
Click Source . From the drop-down menu select Application Browse to select a source site and an application server to view available database recovery points. Select resources, and change the order in which the resources are recovered by dragging and dropping the resources in the grid.
Alternatively, select Application Search from the drop-down menu to search for application servers with available recovery points. Add copies to the job definition by clicking Add. Change the order in which the resources are recovered by dragging and dropping the resources in the grid.
Click Copy . Sites containing copies of the selected data display. Select a site. By default the latest copy of your data is used. To choose a specific version, select a site and click Select Version. Click the Version field to view specific copies and their associated job and completion time. If recovery from one snapshot fails, another copy from the same site is used.
If creating an Instant Database Restore job definition, an additional recovery option is available through the Select Version feature. Enable Allow Point-in-Time selection when job runs to leverage archived logs and enable a point-in-time recovery of the databases.
Click Destination . Select a source site and an associated Microsoft SQL database. Click the New database name field to enter an optional alternate name for the database. If the destination is a SQL Failover Cluster instance, select a Windows server proxy in the Select a Windows server to resignature LUNs section. When resignaturing a copy, ECX retains the data and mounts the volume to the proxy selected in the Select a Windows server to resignature LUNs section.
Note: Any Windows node with iSCSI or Fibre Channel access to the storage can be selected as a proxy server, provided that the node is not part of the original cluster. It is recommended to select a standalone virtual or physical Windows node as a proxy server.
Note:: If creating an Instant Seeding job definition, the seeding target must be a secondary replica. If a primary AlwaysOn node is selected as a seeding target, the job will fail. The New database name option does not apply to Instant Seeding jobs.
To create the job definition using default options, click Create Job. The job can be run manually from the Jobs tab.
To edit options before creating the job definition, click Advanced. Set the job definition options.

Optionally, expand the Notification section to select the job notification options.
SMTP Server
From the list of available SMTP resources, select the SMTP Server to use for job status email notifications. If an SMTP server is not selected, an email is not sent.
Email Address
Enter the email addresses of the status email notifications recipients. Click Add to add it to the list.
Optionally, expand the Schedule section to select the job scheduling options. Select Start job now to create a job definition that starts the job immediately. Select Schedule job to start at later time to view the list of available schedules. Optionally select one or more schedules for the job. As each schedule is selected, the schedule's name and description displays.
Note: To create and select a new schedule, click the Configure tab, then select Schedules . Create a schedule, return to the job editor, refresh the Available Schedules pane, and select the new schedule.
When you are satisfied that the job-specific information is correct, click Create Job. The job runs as defined by your schedule, or can be run manually from the Jobs tab.

NEXT STEPS:

If you do not want to wait until the next scheduled job run, run the job session on demand. See Start, Pause, and Hold a Job Session.
Track the progress of the job session on the Jobs tab. See Monitor a Job Session.
If notification options are enabled, an email message with information about the status of each task is sent when the job completes.