Veeam Backup & Replication 11
Veeam Explorers PowerShell Reference
Related documents
See more documents
Search

Restore-VESQLDatabase

In this article

    Short Description

    Restores backed-up Microsoft SQL databases.

    Applies to Veeam Backup & Replication

    Product Edition: Enterprise, Enterprise Plus, Veeam Universal License

    Syntax

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

    Detailed Description

    This cmdlet restores backed-up Microsoft SQL databases. You can restore Microsoft SQL databases to the original location or to another location.

    Parameters

    Parameter

    Description

    Type

    Required

    Position

    Accept Pipeline Input

    Accept Wildcard Characters

    Database

    Specifies a Microsoft SQL database. The cmdlet will restore this database.

    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.

    String

    False

    Named

    False

    False

    ServerName

    For restore to another location.

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

    String

    False

    Named

    False

    False

    InstanceName

    For restore to another location.

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

    String

    False

    Named

    False

    False

    Port

    Specifies a port number that will be used to connect to the target Microsoft SQL server.

    Int32

    False

    Named

    False

    False

    SqlCredentials

    Specifies SQL credentials to authenticate against a Microsoft SQL server.

    Note: If you do not specify SQL credentials, the cmdlet will use current account credentials. If these credentials do not work, the cmdlet will use credentials specified in the backup job.

    PSCredential

    False

    Named

    False

    False

    UseSQLAuthentication

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

    SwitchParameter

    False

    Named

    False

    False

    GuestCredentials

    Specifies credentials to authenticate against the target Microsoft SQL server. If these 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.

    PSCredential

    False

    Named

    False

    False

    ToPointInTimeUtc

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

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

    Datetime

    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.

    Datetime

    False

    Named

    False

    False

    AvailabilityGroupName

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

    String

    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.

    String

    False

    Named

    False

    False

    File

    Specifies an array of file names 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.

    String[]

    False

    Named

    False

    False

    Force

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

    SwitchParameter

    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.

    RestoreMode

    False

    Named

    False

    False

    StandbyFilePath

    For the StandBy parameter.

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

    String

    False

    Named

    False

    False

    <CommonParameters>

    This cmdlet supports Microsoft PowerShell common parameters. For more information on common parameters, see the About Common Parameters section of Microsoft Docs.

    Examples

    Restore-VESQLDatabaseExample 1. Restoring to Original Location

    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.
    • If these credentials do not work, the cmdlet will use the backup job credentials to connect to the guest OS and to a Microsoft SQL server.

     

    $session = Get-VESQLRestoreSession

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

    Restore-VESQLDatabase -Database $database

    Perform the following steps:

    1. Run the Get-VESQLRestoreSession cmdlet. Save the result to the $session variable.

    The Get-VESQLRestoreSession cmdlet will return an array of restore sessions. Mind the ordinal number of the necessary restore session (in our example, it is the first restore session in the array).

    1. Run the Get-VESQLDatabase cmdlet. Set the $session variable as the Session parameter value. Specify the Name parameter value. Save the result to the $database variable.
    2. Run the Restore-VESQLDatabase cmdlet. Set the $database as the Database parameter value.

    Restore-VESQLDatabaseExample 2. Restoring to Specific Folder

    This example shows how to restore a Microsoft SQL database to the specific folder. The SQL credentials are used to authenticate against the guest OS and a Microsoft SQL server.

    $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

    Perform the following steps:

    1. Run the Get-VESQLRestoreSession cmdlet. Save the result to the $session variable.

    The Get-VESQLRestoreSession cmdlet will return an array of restore sessions. Mind the ordinal number of the necessary restore session (in our example, it is the first restore session in the array).

    1. Run the Get-VESQLDatabase cmdlet. Set the $session as the Session parameter value. Specify the Name parameter value. Save the result to the $database variable.
    2. Run the Get-Credential cmdlet to create a credential object. Enter SQL credentials that will be used to authenticate against the guest OS and Microsoft SQL server. Save the result to the $creds variable.
    3. Run the Restore-VESQLDatabase cmdlet. Specify the following settings:
    • Set the $database variable as the Database value.
    • Specify the ServerName parameter value.
    • Specify the TargetFolder parameter value.
    • Set the $creds as the SqlCredentials parameter value.

    Restore-VESQLDatabaseExample 3. Restoring to Specific Array

    This example shows how to restore Microsoft SQL database files to the specific 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 SQL credentials to authenticate against a Microsoft SQL server.
    • The cmdlet will use the guest credentials to authenticate against the guest OS.

    $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

    Perform the following steps:

    1. Run the Get-VESQLRestoreSession cmdlet. Save the result to the $session variable.

    The Get-VESQLRestoreSession cmdlet will return an array of restore sessions. Mind the ordinal number of the necessary restore session (in our example, it is the first restore session in the array).

    1. Run the Get-VESQLDatabase cmdlet. Set the $session as the Session parameter value. Save the result to the $database variable.
    2. Run the Get-VESQLDatabaseFile cmdlet. Set the $database as the Database parameter value. Save the result to the $files variable.
    3. Use the $files variable to get details about Microsoft SQL database files.
    4. Specify an array of the paths for every database file that you want to restore. Save the result to the $path variable.
    5. Run the Get-Credential cmdlet to create a credential object. Enter SQL credentials that will be used to authenticate against the SQL server. Save the result to the $sqlcreds variable.
    6. Run the Get-Credential cmdlet to create a credential object. Enter guest credentials that will be used to authenticate against guest OS. Save the result to the $oscreds variable.
    7. Run the Restore-VESQLDatabase cmdlet with the variables obtained beforehand.

    Restore-VESQLDatabaseExample 4. Restoring to Latest Restore Point

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

    $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

    Perform the following steps:

    1. Run the Get-VESQLRestoreSession cmdlet. Save the result to the $session variable.

    The Get-VESQLRestoreSession cmdlet will return an array of restore sessions. Mind the ordinal number of the necessary restore session (in our example, it is the first restore session in the array).

    1. Run the Get-VESQLDatabase cmdlet. Set the $session variable as the Session parameter value. Specify the Name parameter. Save the result to the $database variable.
    2. Run the Get-VESQLDatabaseRestoreInterval cmdlet. Set the $database as the Database parameter value. Save the result to the $restoreinterval variable.
    3. Run the Restore-VESQLDatabase cmdlet with the $database variable. Specify the following options:
    • Set the $database as the Database parameter value.
    • Specify the ServerName parameter value.
    • Specify the TargetFolder parameter value.
    • Set the $creds variable as the SqlCredentials parameter value.
    • Set the $restoreinterval variable with the ToUtc property as the ToPointInTime parameter value to specify the latest restore point of the database.

    Related Commands