Restore-Database

DBSystems: Restores a SQL Server database from a backup

Script

#Requires -Version 5.0
#Requires -Modules SQLServer

[CmdLetBinding()]
Param(
    [Parameter(Mandatory = $true)]   
    [string]$ServerInstance,    
    [Parameter(Mandatory = $true)]   
    [string]$DBName,
    [Parameter(Mandatory = $true)]   
    [string]$BackupFile,
    [pscredential]$ServerCredential,
    [ValidateSet('Database', 'Files', 'Log',  'OnlinePage', 'OnlineFiles')]
    [string]$RestoreAction = "Database",
    [switch]$CheckSum,
    [switch]$ClearSuspectPageTable,
    [switch]$ContinueAfterError,
    [string]$DatabaseFile,
    [string]$DatabaseFileGroup,
    [switch]$KeepReplication,
    [switch]$NoRecovery,
    [switch]$ReplaceDatabase,
    [datetime]$ToPointInTime,
    [int]$ConnectionTimeout = 30
)

function Get-SqlServerInstanceInternal {
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory = $true)]   
        [string]$ServerInstance,    
        [pscredential]$ServerCredential,
        [int]$ConnectionTimeout = 30
    )
    try {
        [hashtable]$cmdArgs = @{
            'ErrorAction' = 'Stop'
            'Confirm' = $false
            'ServerInstance' = $ServerInstance
            'ConnectionTimeout' = $ConnectionTimeout
        }
        if ($null -ne $ServerCredential) {
            $cmdArgs.Add('Credential', $ServerCredential)
        }
        return Get-SqlInstance @cmdArgs
    } catch {
        throw
    }
}

Import-Module SQLServer

try {    
    $instance = Get-SqlServerInstanceInternal -ServerInstance $ServerInstance -ServerCredential $ServerCredential -ConnectionTimeout $ConnectionTimeout

    [hashtable]$cmdArgs = @{
        'ErrorAction' = 'Stop'
        'InputObject' = $instance
        'Database'    = $DBName
        'Confirm'     = $false
        'RestoreAction' = $RestoreAction
        'CheckSum'    = $CheckSum
        'ClearSuspectPageTable' = $ClearSuspectPageTable
        'ContinueAfterError' = $ContinueAfterError
        'BackupFile'  = $BackupFile
        'KeepReplication' = $KeepReplication
        'ReplaceDatabase' = $ReplaceDatabase
        'NoRecovery'  = $NoRecovery
        'PassThru'    = $true
    }

    if ($RestoreAction -eq 'Files') {
        if (-not [string]::IsNullOrWhiteSpace($DatabaseFile)) {
            $cmdArgs.Add('DatabaseFile', $DatabaseFile.Split(',').Trim())
        }
        elseif (-not [string]::IsNullOrWhiteSpace($DatabaseFileGroup)) {
            $cmdArgs.Add('DatabaseFileGroup', $DatabaseFileGroup.Split(',').Trim())
        }
    }
    elseif ($RestoreAction -eq 'Log') {
        if ($null -ne $ToPointInTime) {
            $cmdArgs.Add('ToPointInTime', $ToPointInTime)
        }
    }

    $result = Restore-SqlDatabase @cmdArgs | Select-Object *    
    Write-Output $result
} catch {
    throw
}

Customize

ServerInstance string

Specifies the name of the target computer including the instance name, e.g. MyServer\Instance

DBName string

Specifies the name of the database to restore

BackupFile string

Specifies the location and file name of the backup file to restore from

ServerCredential pscredential

Specifies a PSCredential object for the connection to the SQL Server. ServerCredential is ONLY used for SQL Logins. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login.

RestoreAction string

Specifies the type of restoration operation: Database, Files, Log, OnlinePage, or OnlineFiles

CheckSum switch

Off

Indicates that a checksum value is calculated during the restore operation

ClearSuspectPageTable switch

Off

Indicates that the suspect page table is deleted after the restore operation

ContinueAfterError switch

Off

Indicates that the operation continues when a checksum error occurs

DatabaseFile string

Specifies specific database files to restore, comma separated. Only used when RestoreAction is 'Files'.

DatabaseFileGroup string

Specifies specific database file groups to restore, comma separated. Only used when RestoreAction is 'Files'.

KeepReplication switch

Off

Indicates that the replication configuration is preserved during restore

NoRecovery switch

Off

Indicates that the database is left in the restoring state (NORECOVERY)

ReplaceDatabase switch

Off

Indicates that any existing database with the same name should be overwritten (WITH REPLACE)

ToPointInTime datetime

Specifies the endpoint for database log restoration (Point-in-time recovery)

ConnectionTimeout int

Specifies the time period to retry the command on the target server