Restore SQL Server database backups
Suggest changes
PDFs
You can use SnapCenter to restore backed-up SQL Server databases. Database restoration is a multiphase process that copies all of the data and log pages from a specified SQL Server backup to a specified database.
-
You can restore the backed-up SQL Server databases to a different SQL Server instance on the same host where the backup was created.
You can use SnapCenter to restore the backed-up SQL Server databases to an alternate path so that you do not replace a production version.
-
SnapCenter can restore databases in a Windows cluster without taking the SQL Server cluster group offline.
-
If a cluster failure (a cluster group move operation) occurs during a restore operation (for example, if the node that owns the resources goes down), you must reconnect to the SQL Server instance, and then restart the restore operation.
-
You cannot restore the database when the users or the SQL Server Agent jobs are accessing the database.
-
You cannot restore system databases to an alternate path.
-
The SCRIPTS_PATH is defined using the PredefinedWindowsScriptsDirectory key located in the SMCoreServiceHost.exe.Config file of the plug-in host.
If needed, you can change this path and restart SMcore service. It is recommended that you use the default path for security.
The value of the key can be displayed from swagger through the API: API /4.7/configsettings
You can use the GET API to display the value of the key. SET API is not supported.
-
Most of the fields on the Restore wizard pages are self-explanatory. The following information describes fields for which you might need guidance.
-
For SnapMirror active sync restore operation, you must select the backup from the primary location.
-
For SnapLock enabled policies, for ONTAP 9.12.1 and below version, if you specify a Snapshot locking period, the clones created from the tamper proof Snapshots as part of restore will inherit the SnapLock expiry time. Storage admin should manually cleanup the clones post the SnapLock expiry time.
-
In the left navigation pane, click Resources, and then select the appropriate plug-in from the list.
-
In the Resources page, select either Database or Resource Group from the View list.
-
Select the database or the resource group from the list.
The topology page is displayed.
-
From the Manage Copies view, select Backups from the storage system.
-
Select the backup from the table, and then click the
icon.
-
In the Restore Scope page, select one of the following options:
Option Description Restore the database to the same host where the backup was created
Select this option if you want to restore the database to the same SQL server where the backups are taken.
Restore the database to an alternate host
Select this option if you want the database to be restored to a different SQL server in the same or different host where backups are taken.
Select a host name, provide a database name (optional), select an instance, and specify the restore paths.
The file extension provided in the alternate path must be same as the file extension of the original database file. If the Restore the database to an alternate host option is not displayed in the Restore Scope page, clear the browser cache.
Restore the database using existing database files
Select this option if you want the database to be restored to an alternate SQL Server in the same or different host where backups are taken.
Database files should be already present on the given existing file paths. Select a host name, provide a database name (optional), select an instance, and specify the restore paths.
-
In the Recovery Scope page, select one of the following options:
Option Description None
Select None when you need to restore only the full backup without any logs.
All log backups
Select All log backups up-to-the-minute backup restore operation to restore all of the available log backups after the full backup.
By log backups until
Select By log backups to perform a point-in-time restore operation, which restores the database based on backup logs until the backup log with the selected date.
By specific date until
Select By specific date until to specify the date and time after which transaction logs are not applied to the restored database.
This point-in-time restore operation halts the restoration of transaction log entries that were recorded after the specified date and time.
Use custom log directory
If you have selected All log backups, By log backups, or By specific date until and the logs are located at a custom location, select Use custom log directory, and then specify the log location.
The Use Custom log directory option is available only if you have selected Restore the database to an alternate host or Restore the database using existing database files. You can also use the shared path but ensure that the path is accessible by the SQL user.
The custom log directory is not supported for availability group database. -
In the Pre Ops page, perform the following steps:
-
In the Pre Restore Options page, select one of the following options:
-
Select Overwrite the database with same name during restore to restore the database with the same name.
-
Select Retain SQL database replication settings to restore the database and retain the existing replication settings.
-
Select Create transaction log backup before restore to create a transaction log before the restore operation begins.
-
Select Quit restore if transaction log backup before restore fails to abort the restore operation if the transaction log backup fails.
-
-
Specify optional scripts to run before performing a restore job.
For example, you can run a script to update SNMP traps, automate alerts, send logs, and so on.
The prescripts or postscripts path should not include drives or shares. The path should be relative to the SCRIPTS_PATH.
-
-
In the Post Ops page, perform the following steps:
-
In the Choose database state after restore completes section, select one of the following options:
-
Select Operational, but unavailable for restoring additional transaction logs if you are restoring all of the necessary backups now.
This is the default behavior, which leaves the database ready for use by rolling back the uncommitted transactions. You cannot restore additional transaction logs until you create a backup.
-
Select Non-operational, but available for restoring additional transactional logs to leave the database non-operational without rolling back the uncommitted transactions.
Additional transaction logs can be restored. You cannot use the database until it is recovered.
-
Select Read-only mode, available for restoring additional transactional logs to leave the database in read-only mode.
This option undoes uncommitted transactions, but saves the undone actions in a standby file so that recovery effects can be reverted.
If the Undo directory option is enabled, more transaction logs are restored. If the restore operation for the transaction log is unsuccessful, the changes can be rolled back. The SQL Server documentation contains more information.
-
-
Specify optional scripts to run after performing a restore job.
For example, you can run a script to update SNMP traps, automate alerts, send logs, and so on.
The prescripts or postscripts path should not include drives or shares. The path should be relative to the SCRIPTS_PATH.
-
-
In the Notification page, from the Email preference drop-down list, select the scenarios in which you want to send the emails.
You must also specify the sender and receiver email addresses, and the subject of the email.
-
Review the summary, and then click Finish.
-
Monitor the restore process by using the Monitor > Jobs page.
-
Initiate a connection session with the SnapCenter Server for a specified user by using the Open-SmConnection cmdlet.
PS C:\> Open-Smconnection
-
Retrieve the information about the one or more backups that you want to restore by using the Get-SmBackup and Get-SmBackupReport cmdlets.
This example displays information about all available backups:
PS C:\> Get-SmBackup BackupId BackupName BackupTime BackupType -------- ---------- ---------- ---------- 1 Payroll Dataset_vise-f6_08... 8/4/2015 11:02:32 AM Full Backup 2 Payroll Dataset_vise-f6_08... 8/4/2015 11:23:17 AM
This example displays detailed information about the backup from January 29th 2015 to February 3rd, 2015:
PS C:\> Get-SmBackupReport -FromDateTime "1/29/2015" -ToDateTime "2/3/2015" SmBackupId : 113 SmJobId : 2032 StartDateTime : 2/2/2015 6:57:03 AM EndDateTime : 2/2/2015 6:57:11 AM Duration : 00:00:07.3060000 CreatedDateTime : 2/2/2015 6:57:23 AM Status : Completed ProtectionGroupName : Clone SmProtectionGroupId : 34 PolicyName : Vault SmPolicyId : 18 BackupName : Clone_SCSPR0019366001_02-02-2015_06.57.08 VerificationStatus : NotVerified SmBackupId : 114 SmJobId : 2183 StartDateTime : 2/2/2015 1:02:41 PM EndDateTime : 2/2/2015 1:02:38 PM Duration : -00:00:03.2300000 CreatedDateTime : 2/2/2015 1:02:53 PM Status : Completed ProtectionGroupName : Clone SmProtectionGroupId : 34 PolicyName : Vault SmPolicyId : 18 BackupName : Clone_SCSPR0019366001_02-02-2015_13.02.45 VerificationStatus : NotVerified
-
Restore data from the backup by using the Restore-SmBackup cmdlet.
Restore-SmBackup -PluginCode 'DummyPlugin' -AppObjectId 'scc54.sccore.test.com\DummyPlugin\NTP\DB1' -BackupId 269 -Confirm:$false output: Name : Restore 'scc54.sccore.test.com\DummyPlugin\NTP\DB1' Id : 2368 StartTime : 10/4/2016 11:22:02 PM EndTime : IsCancellable : False IsRestartable : False IsCompleted : False IsVisible : True IsScheduled : False PercentageCompleted : 0 Description : Status : Queued Owner : Error : Priority : None Tasks : {} ParentJobID : 0 EventId : 0 JobTypeId : ApisJobKey : ObjectId : 0 PluginCode : NONE PluginName :
The information regarding the parameters that can be used with the cmdlet and their descriptions can be obtained by running Get-Help command_name. Alternatively, you can also refer to the SnapCenter Software Cmdlet Reference Guide.