Help Center
Choose product document...
Veeam Backup & Replication 9.5 Update 4
Veeam Explorers PowerShell Reference

Restore-VESQLDatabase

Short Description

Restores Microsoft SQL databases.

Applies to

Product Edition: Enterprise, Enterprise Plus

Syntax

Restore-VESQLDatabase [-Database] <VESQLDatabase> [-DatabaseName <string>] [-ServerName <string>] [-InstanceName <string>] [-Port <int>] [-SqlCredentials <pscredential>] [-UseSQLAuthentication] [-GuestCredentials <pscredential>] [-ToPointInTime <datetime>] [-AvailabilityGroupName <string>] [-TargetFolder <string>] [-File <VESQLDatabaseFile[]>] [-TargetPath <string[]>] [-Force] [-RecoveryState <RestoreMode> {Recovery | NoRecovery | StandBy}] [-StandbyFilePath <string>]  [<CommonParameters>]

Detailed Description

This cmdlet allows you to restore Microsoft SQL databases. You can restore Microsoft SQL databases to the original or to another location.

Parameters

Parameter

Description

Required

Position

Accept
Pipeline
Input

Accept
Wildcard
Characters

Database

Specifies a Microsoft SQL database that you want to restore.

Accepts the VESQLDatabase type. To get this object, run the Get-VESQLDatabase cmdlet.

True

0

True (ByValue)

False

DatabaseName

For restore to another location. Specifies a name for a Microsoft SQL database. The cmdlet will restore the database with the specified name.

False

Named

False

False

ServerName

For restore to another location. Specifies a name of a target server. The cmdlet will restore a Microsoft SQL database to the specified target server.

False

Named

False

False

InstanceName

For restore to another location. Specifies the name of a target instance. The cmdlet will restore a Microsoft SQL database to the specified target instance.

False

Named

False

False

Port

Specifies a port number. The cmdlet will use this port to connect to the target Microsoft SQL server.

False

Named

False

False

SqlCredentials

Specifies credentials to authenticate against a Microsoft SQL server.

NOTE: If you do not specify SQL credentials, the cmdlet will use current account credentials. The cmdlet will use credentials, specified in the backup job in the following scenarios:

  • If current account credentials do not work
  • If you omit the Server parameter

False

Named

False

False

UseSQLAuthentication

Indicates that the cmdlet will use the SQL authentication to connect to the target Microsoft SQL server.

NOTE: If you omit this parameter, the cmdlet will use credentials specified in the SqlCredentials parameter to connect to both the Microsoft SQL server and to the guest OS.

False

Named

False

False

GuestCredentials

Specifies credentials to authenticate against the target server. In case the specified credentials do not work, the cmdlet will use credentials, specified in the backup job.

NOTE: If you omit this parameter, the cmdlet will use credentials specified in the SqlCredentials parameter or current account credentials to connect to both the Microsoft SQL server and to the guest OS.

False

Named

False

False

ToPointInTime

Specifies the point in time within a restore interval of a Microsoft SQL database.

The cmdlet will restore the database to the state of the specified point in time.

False

Named

False

False

Availability
GroupName

Specifies the name of the AlwaysOn availability group. The cmdlet will add a Microsoft SQL database to the specified group.

False

Named

False

False

TargetFolder

For restore Microsoft SQL database files to one location.

Specifies the folder. The cmdlet will restore all database files to that folder.

NOTE: This parameter is not available if you use the TargetPath parameter.

False

Named

False

False

File

Specifies an array of filenames for Microsoft SQL databases.

Accepts the VESQLDatabaseFile[] type. To get this object, run the Get-VESQLDatabaseFile cmdlet.

False

Named

False

False

TargetPath

Specifies a target path array. The cmdlet will restore Microsoft SQL database files to locations, specified in the target path array.

NOTE:

  • For every Microsoft SQL database file, you must assign the specific file path.
  • This parameter is not available if you use the TargetFolder parameter.

False

Named

False

False

Force

Indicates that the cmdlet will overwrite an existing Microsoft SQL database with a Microsoft SQL database from a backup.

NOTE: The cmdlet will show no prompt before executing the command.

False

Named

False

False

RecoveryState

Specifies the restore scenario.

  • Recovery - use this option to leave a Microsoft SQL database in the ready to use state. With this option, you will not be able to restore additional transaction logs.
  • NoRecovery - use this option to leave a Microsoft SQL database in the non-operational state and not to roll back uncommitted transactions. Use this option to restore transaction logs that are backed up with the third-party tool.
  • StandBy - use this option to leave a Microsoft SQL database in the read-only mode. It allows you to undo uncommitted transactions. The undo actions are saved in a standby file so that the recovery effects can be reversed.

Default: Recovery.

False

Named

False

False

StandbyFilePath

For the StandBy parameter.

Specifies a location for the standby file with the uncommitted transactions.

False

Named

False

False

<CommonParameters>

This cmdlet supports Microsoft PowerShell common parameters. For more information on common parameters, see Microsoft Docs.

Example 1

This example shows how to restore a Microsoft SQL database to the original location. The restore session will run with the following settings:

  • The cmdlet will use credentials of the user that is running the PowerShell session.
  • In case these credentials do not work, the cmdlet will use the backup job credentials to connect to Windows OS and to a Microsoft SQL server.

You must perform the following steps:

  1. Run the Get-VESQLRestoreSession cmdlet to get a restore session for a Microsoft SQL database. Save the result to the $session variable.
  2. Run the Get-VESQLDatabase cmdlet with the $session variable to get the Microsoft SQL database that you want to restore. Save the result to the $database variable.
  3. Run the Restore-VESQLDatabase cmdlet with the $database variable.

$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name "SQLDatabase"

Restore-VESQLDatabase -Database $database

Example 2

This example shows how to restore a Microsoft SQL database to the specific folder. To authenticate against a Microsoft SQL server and Windows OS, the cmdlet will use the same type of credentials.

  1. Run the Get-VESQLRestoreSession cmdlet to get a restore session for a Microsoft SQL database. Save the result to the $session variable.
  2. Run the Get-VESQLDatabase cmdlet with the $session variable to get the Microsoft SQL database that you want to restore. Save the result to the $database variable.
  3. Run the Get-Credential cmdlet to create a credential object that you want to use for authenticating with the Microsoft SQL server and Windows OS. Save the result to the $creds variable.
  4. Run the Restore-VESQLDatabase cmdlet with the $database variable. Use the TargetFolder parameter to specify the restore location.

$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name "SQLDatabase"

$creds = Get-Credential

Restore-VESQLDatabase -Database $database -ServerName "SQLServer" -TargetFolder "C:\SQL\Restore" -SqlCredentials $creds

Example 3

This example shows how to restore Microsoft SQL database files to the specified array. The restore session will run with the following options:

  • The cmdlet will restore the File1.mdf file to the C:\SQLDBfile1\new_file.mdf location.
  • The cmdlet will restore the File2.ldf  file to the C:\SQLDBfile2\new_file.ldf location.
  • The cmdlet will use different types of credentials to authenticate with a Microsoft SQL server and Windows OS.

You must perform the following steps:

  1. Run the Get-VESQLRestoreSession cmdlet to get a restore session for a Microsoft SQL database. Save the result to the $session variable.
  2. Run the Get-VESQLDatabase cmdlet with the $session variable to get the Microsoft SQL database that you want to restore. Save the result to the $database variable.
  3. Run the Get-VESQLDatabaseFile cmdlet to get an array of Microsoft SQL database files that you want to restore. Save the result to the $files variable.
  4. Use the $files variable to get details about Microsoft SQL database files.
  5. Specify an array of the paths for every database file that you want to restore. Save the result to the $path variable.
  6. Run the Get-Credential cmdlet to create a credential object that you want to use for authenticating with the SQL server. Save the result to the $sqlcreds variable.
  7. Run the Get-Credential cmdlet to create a credential object that you want to use for authenticating with Windows OS. Save the result to the $oscreds variable.
  8. Run the Restore-VESQLDatabase cmdlet with the variables obtained beforehand.

$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name "SQLDatabase"

$files = Get-VESQLDatabaseFile -Database $database

$files

Path                                               Type

----                                               ----

C:\Program Files\File1.mdf                         Primary

C:\Program Files\File2.ldf                         Secondary

$path = @("C:\SQLDBfile1\new_file.mdf","C:\SQLDBfile2\new_file.ldf")

$sqlcreds = Get-Credential

$oscreds = Get-Credential

Restore-VESQLDatabase -Database $database -ServerName "SQLServer" -File $files -TargetPath $path -SqlCredentials $sqlcreds -GuestCredential $oscreds -UseSqlAuthentication

Example 4

This example shows how to restore Microsoft SQL database files to the latest restore point.

  1. Run the Get-VESQLRestoreSession cmdlet to get a restore session for a Microsoft SQL database. Save the result to the $session variable.
  2. Run the Get-VESQLDatabase cmdlet with the $session variable to get the Microsoft SQL database that you want to restore. Save the result to the $database variable.
  3. Run the Get-VESQLDatabaseRestoreInterval cmdlet with the $database variable to get details about an available restore period of the Microsoft SQL database. Save the result to the $restoreinterval variable.
  4. Run the Restore-VESQLDatabase cmdlet with the $database variable. Specify the following options:
  • Use the TargetFolder parameter to specify the restore location
  • Use the $restoreinterval variable with the ToUtc property for the ToPointInTime parameter to specify the latest restore point of the database.

$session = Get-VESQLRestoreSession

$database = Get-VESQLDatabase -Session $session[0] -Name "SQLDatabase"

$restoreinterval = Get-VESQLDatabaseRestoreInterval -Database $database

Restore-VESQLDatabase -Database $database -ServerName "SQLServer" -TargetFolder "C:\SQL\Restore" -SqlCredentials $creds -ToPointInTime $restoreinterval.ToUtc

Related Commands

Veeam Large Logo

User Guide for VMware vSphere

User Guide for Microsoft Hyper-V

Enterprise Manager User Guide

Veeam Cloud Connect Guide

Veeam Agent Management Guide

Veeam Explorers User Guide

Backup and Restore of SQL Server Databases

Veeam Plug-ins for Enterprise Applications

PowerShell Reference

Veeam Explorers PowerShell Reference

RESTful API Reference

Required Permissions

Veeam Availability for Nutanix AHV

Veeam Backup for Microsoft Office 365 Documentation

Veeam ONE Documentation

Veeam Agent for Windows Documentation

Veeam Agent for Linux Documentation

Veeam Management Pack Documentation