Restore Database with SMO

Probably 3 or 4 versions of this already on here.  So here is one more.  

function Invoke-Restore{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [string]$ServerName,
        [Parameter(Mandatory)]
        [int]$Port,
        [string]$DatabaseName = $null,
        [string]$BackupFile = "c:\PathToBackupFile.bak",
        [string]$dataDir = "d:\mssql\data",
        [string]$logDir = "d:\mssql\tranlog",
        [switch]$Force
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
 

    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$ServerName,$Port"
        $backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
         
        $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
        $restore.Devices.Add($backup);
        $restore.NoRecovery = $false;
        $restore.ReplaceDatabase = $true
 
        $dbFiles = $restore.ReadFileList($srv);
        $header = $restore.ReadBackupHeader($srv)
        if([System.String]::IsNullOrWhiteSpace($DatabaseName)){
            $DatabaseName = $header.DatabaseName
        }
        $restore.Database = $DatabaseName
        if($Force -and $srv.Databases[$DatabaseName]){
            $srv.KillDatabase($DatabaseName);
        }

        foreach($dbFile in $dbFiles){
            $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
            switch($dbFile.Type){
                'D'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$dataDir\$fileName")))
                }
                'L'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$logDir\$fileName")))
                }
            }
        }
    
        $srv.ConnectionContext.StatementTimeout = 0;
        $restore.SqlRestore($srv);
        $srv.ConnectionContext.Disconnect();

    }
    catch{
        $_ | fl -Force
    }

}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.