Month: November 2018

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
    }

}

Add an AvailabilityDatabase

function Add-AvailabilityGroupDatabase{
    param(
        $ServerName,
        $Port = 1433,
        $DatabaseName,
        $AvailabilityGroup
    )
    begin{

    }
    process{

        if(!$ServerName.Contains('.')){
            $ServerName = "$ServerName.$env:USERDNSDOMAIN"
        }
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$ServerName,$Port"
        $ServerName = $srv.AvailabilityGroups[$AvailabilityGroup].PrimaryReplicaServerName
        $srv.ConnectionContext.Disconnect()
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$($ServerName).$env:USERDNSDOMAIN,$Port"

        if(!$srv.Databases[$DatabaseName]){
            throw "Could not find a database $DatabaseName on primary replica $ServerName"
        }

        if(!$srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityDatabases.Contains($DatabaseName)){
            [void]$srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$DatabaseName] WITH RECOVERY;");
            $ADB = New-Object Microsoft.SqlServer.Management.smo.AvailabilityDatabase($srv.AvailabilityGroups[$AvailabilityGroup], $DatabaseName);
            $ADB.Create();
        }
        else{
            Write-Warning "Database $DatabaseName is already an availbility database on server $ServerName."
        }
        
        
        $srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityReplicas | ?{$_.Name -ne $ServerName} | select -expandproperty Name | %{
            $name = $_ 
            try{
                $sec = new-object Microsoft.SqlServer.Management.smo.Server "$($name).$env:USERDNSDOMAIN,$Port"
                $ag = $sec.AvailabilityGroups[$AvailabilityGroup]
                if(!$ag.AvailabilityDatabases[$DatabaseName].IsJoined){
                    $ag.AvailabilityDatabases[$DatabaseName].JoinAvailablityGroup();
                }
                else{
                    Write-Warning "Database $DatabaseName is already an availbility database on server $name."
                }
            }
            catch{
                Write-Error $_ | fl -Force
            }
            finally{
                $sec.ConnectionContext.Disconnect();
            }
        }


        $srv.ConnectionContext.Disconnect()

    }
    end{

    }
}