PowerShell: Restore SQL Database from Full and/or Differential Backups

$databaseName='BALOO_MSCRM'
$newDatabaseName='BALOO_MSCRM'
$dbData='mscrm' # set this value to $null for autogen defaults
$dbLog='mscrm_log' # set this value to $null for autogen defaults

function restoreDatabase{
    param(
        $databaseName,
        $dbData,
        $dbLog,
        $newDatabaseName
    )

    if(!$dbData){$dbData=$databaseName}
    if(!$dbLog){$dbData=$databaseName+'_log'}
    if(!$newDatabaseName){$newDatabaseName=$databaseName}
    function pickIndex($list){
        function displayList($list){
            for ($i=0;$i -lt $list.count;$i++){
                    write-host "$i`: $(($list[$i]|out-string).trim())"
            }
        }
        
        do {
            displayList $list
            try {
                $flag = $true
                [int]$pick=Read-Host -Prompt "`n--------------------------------------------------------`nPlease type the number corresponding to the desired item`n--------------------------------------------------------"
                } # end try
            catch {$flag = $false}
        } until ($pick -lt $list.count -and $flag)    
        $pickIndex=$pick
        $pickedItem=$list[$pick]  
        write-host "Picked index is $pickIndex, which corresponds to: $pickedItem"
        return $pickIndex
    }

    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;                
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                Clear-Host;
                write-host $($content|out-string).Trim()
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }

    <# SQLSERVER module is not being used in this iteration because the Get-SqlBackupHistory does not show backup file locations
    if(!(get-module sqlserver -ea Silentlycontinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        #The Tls12 setting preempts this error:
        #WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'.
        #PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlserver'.
        #Try Get-PSRepository to see all available registered module repositories.
        #At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:1772 char:21
        #+ ...          $null = PackageManagement\Install-Package @PSBoundParameters
        #+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        #    + CategoryInfo          : ObjectNotFound: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package], Exception
        #    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage
        install-module sqlserver -allowclobber -force
    }
    $backups=Get-SqlBackupHistory -ServerInstance $env:computername -DatabaseName $databaseName
    $backupDates=$backups.BackupStartDate
    $pickIndex=pickIndex $backupDates
    $backupItem=$backups[$pickIndex]
    #>

    import-module sqlps
    $getBackups="-- Get Backup History for required database
        USE $databaseName
        GO
        SELECT TOP 100
        s.database_name,
        m.physical_device_name AS location,
        CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
        CAST(DATEDIFF(second, s.backup_start_date,
        s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
        s.backup_start_date,
        CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
        CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
        CASE s.[type] WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END AS BackupType,
        s.server_name,
        s.recovery_model
        FROM msdb.dbo.backupset s
        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
        WHERE s.database_name = DB_NAME() -- Remove this line for all the database
        ORDER BY backup_start_date ASC, backup_finish_date
        GO
    "
    $backups=invoke-sqlcmd $getBackups
    $backupDates=$backups.backup_start_date
    $pickIndex=pickIndex $backupDates
    $backupItem=$backups[$pickIndex]
    $backupFile=$backupItem.location
    #This SQLSERVER module cmdlet is not being used
    #Restore-SqlDatabase -ServerInstance "." -Database $databaseName -BackupFile $backupItem.location

    <# Some errors that have to do with DB being in use. Also, WITHNORECOVERY option must specified for FULL restores.
    Restore-SqlDatabase : System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no
    files are ready to rollforward.
    At line:1 char:1
    + Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
        + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

    Restore-SqlDatabase : System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
    At line:1 char:1
    + Restore-SqlDatabase -ServerInstance "." -Database $databaseName -Back ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
        + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

    #>

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            $sqlDefaults=@{
                'dataDirectory'=$defaultDataDirectory
                'logDirectory'=$defaultLogDirectory
                'backupDirectory'=$defaultBackupDirectory
                }

    $backupFileIsComplete=if($backupFile -match 'bak$'){$true}else{$false}
    if($backupFileIsComplete){
        $restoreDatabase="
            USE master
            GO
            ALTER DATABASE $databaseName
                SET SINGLE_USER
                WITH ROLLBACK IMMEDIATE
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH FILE = 1,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
        "
    }else{
        $lastFullBackup=.{
            $backupLocations=$backups.location
            $resultFound=$false
            $currentIndex=$pickIndex-1
            Do{
                $currentIndex-=1
                if($backupLocations[$currentIndex] -match 'bak$'){
                    $resultFound=$true
                    return $backupLocations[$currentIndex]
                }
            }until($resultFound)
        }    
        $restoreDatabase="
            USE master
            GO
            ALTER DATABASE $databaseName
                SET SINGLE_USER
                WITH ROLLBACK IMMEDIATE
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$lastFullBackup' WITH NORECOVERY,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
            RESTORE DATABASE [$databaseName] FROM DISK = N'$backupFile' WITH RECOVERY,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO        
        "
    }

    $confirmed=confirmation -content $restoreDatabase
    if($confirmed){
        try{
            invoke-sqlcmd -Query $restoreDatabase -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
            return $true
        }catch{
            write-warning $_
            return $false
        }
    }else{
        write-host 'No confirmations received. No changes have been made.'
        return $false
    }
}

restoreDatabase $databaseName $dbData $dbLog $newDatabaseName

Leave a Reply

Your email address will not be published. Required fields are marked *