Restore-DbaSystemDatabase wanted for 1.0

[potatoqualitee]

Is this a feature OR bug:

feature

Details

Hey @Stuart-Moore - would you be able to work on this?

Here are some tutorials:

https://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx https://msdn.microsoft.com/en-us/library/ms345408(v=sql.130).aspx

I tried once and then ran out of time because I got caught up trying to offer what was essentially Move-DbaSystemDatabase (which was hell).

Here's what I had when I said F it. I couldn't get it to work consistently enough but I think it only was annoying for the move and not the restore.

Please ignore the bad practices -- this is from 3 years ago.

Function Restore-SQLSystemDatabase {
        <#
            .SYNOPSIS
            Restores master, model and msdb.
            .EXAMPLE
             Restore-SQLSystemDatabase $servername $dbname $backupfile $systemdir
            .OUTPUTS
                $true if success
                $true if failure
        #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$servername,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$dbname,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$backupfile,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$systemdir

        )

    $restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
    $restore.ReplaceDatabase = $true

    switch ($dbname) {
        "master" {  $datalogical = "master"; $dataphysical = "$systemdir\master.mdf"
                    $loglogical = "mastlog"; $logphysical = "$systemdir\mastlog.ldf" }
        "model" {   $datalogical = "modeldev"; $dataphysical = "$systemdir\model.mdf"
                    $loglogical = "modellog"; $logphysical = "$systemdir\modellog.ldf" }
        "msdb" {    $datalogical = "MSDBData"; $dataphysical = "$systemdir\MSDBData.mdf"
                    $loglogical = "MSDBLog"; $logphysical = "$systemdir\MSDBLog.ldf" }
    }

    $movedatafile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile" 
    $movedatafile.LogicalFileName = $datalogical
    $movedatafile.PhysicalFileName = $dataphysical
    $null = $restore.RelocateFiles.Add($movedatafile)

    $movelogfile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile" 
    $movelogfile.LogicalFileName = $loglogical
    $movelogfile.PhysicalFileName = $logphysical
    $null = $restore.RelocateFiles.Add($movelogfile)

    try {
        $restore.ReplaceDatabase = $true
        $restore.Database = $dbname
        $restore.Action = "Database"
        $restore.NoRecovery = $false
        $device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
        $device.name = $backupfile
        $device.devicetype = "File"
        $restore.Devices.Add($device)
        $restore.sqlrestore($servername)
        return $true
    } catch { return $false }
}

Function Restore-SQLMasterDB {
 <#
    .SYNOPSIS
        Restoring the master database requires starting up in a specific mode. This function changes the startup params, 
        restarts sql, and restores the database.
    .EXAMPLE
        $result = Restore-SQLMasterDB $server $sqlservice $backupfile $startparams
    .OUTPUTS
        true or false

 #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$server,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$sqlservice,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$master,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$startparams

        )

    $systemdir = $server.MasterDBPath
    $netbiosname = Get-NetBIOSName $server
    $servername = $server.name
    $instance = ($servername.split("\"))[1]
    if ($instance -eq $null) { $instance = "MSSQLSERVER" }

    Write-Host "Changing startup parameters to allow restore of master db." -ForegroundColor Yellow
    $sqlservice.StartupParameters = "-m;$($startparams)"; $sqlservice.Alter()

    Write-Warning "Attempting to restart SQL Services.."
    if ($server.isclustered) {
        try {
                $remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
                $clusteredservices = Get-ClusterResource -Cluster $servername | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
                $clusteredservices | Stop-ClusterResource
                try { $remotesqlservice | Start-Service } catch { throw "bullshit" }
        } catch { 
            Write-Host "SQL Service is clustered and Service Control Manager could not be contacted. Quitting." -ForegroundColor Red
            return $false
        }
    } else {
        Stop-SQLService $sqlservice
        Start-SQLservice $sqlservice
    }

    $sqlservice.StartupParameters = "-T3608;-m;$($startparams)"
    $sqlservice.Alter()

    Write-Host "Restoring master" -ForegroundColor Yellow
    $result = Restore-SQLSystemDatabase $servername "master" $master $systemdir
    return $result
}

Function Start-ReplaceSystemDBs {
 <#
    .SYNOPSIS
        Performs all of the steps needed to restore master, msdb and model.
    .EXAMPLE
        Start-ReplaceSystemDBs $server $RestoreFromDirectory
    .OUTPUTS
        true or false
 #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$server,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$RestoreFromDirectory
        )

    $servername = $server.name
    $netbiosname = Get-NetBIOSName $server
    $instance = ($servername.split("\"))[1]
    if ($instance -eq $null) { $instance = "MSSQLSERVER" }

    $server.ConnectionContext.StatementTimeout = 0

    $master = Get-ChildItem "$RestoreFromDirectory\master\FULL\*.bak" | sort LastWriteTime | select -last 1
    $msdb = Get-ChildItem "$RestoreFromDirectory\msdb\FULL\*.bak" | sort LastWriteTime | select -last 1
    $model = Get-ChildItem "$RestoreFromDirectory\model\FULL\*.bak" | sort LastWriteTime | select -last 1

    Write-Host "Getting master backup version" -ForegroundColor Yellow
    $restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
    $device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem $master, "FILE"
    $restore.Devices.Add($device)
    $backupinfo = $restore.ReadBackupHeader($server)
    $backupversion = [version]("$($backupinfo.SoftwareVersionMajor).$($backupinfo.SoftwareVersionMinor).$($backupinfo.SoftwareVersionBuild)")

    if ($backupversion -ne $server.version) {
            Write-Warning "Not the same version (backup: $backupversion, server: $($server.version)). Cannot restore system dbs."
            return $false
    }

    if ($server.isclustered) {
        try {
            $remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
            $clusteredservices = Get-ClusterResource -Cluster $servername | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
        } catch { 
            Write-Host "SQL Service is clustered and Service Control Manager could not be contacted. Quitting." -ForegroundColor Red
            return $false
        }
    }

    Write-Host "Master backup version and SQL Server match. Going forward." -ForegroundColor Green

    $null = [Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

    $sqlserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $netbiosname
    $systemdir = $server.MasterDBPath
    $sqlservice = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" })
    $sqlagent = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server Agent ($instance)" })

    Write-Host "Backing up current master just in case.." -ForegroundColor Yellow
    $currentmasterbackup = "$RestoreFromDirectory\master\FULL\ps-migrationmaster.bak"
    $sql = "BACKUP DATABASE [master] TO  DISK = N'$currentmasterbackup' WITH NOFORMAT, INIT"
    try { 
        $server.databases['master'].ExecuteNonQuery($sql)
        Write-Host "Successfully backed up master database" -ForegroundColor Green
    } catch {
        Write-Warning "Couldn't backup master. Aborting."
        return $false
    }

    $startparams = $sqlservice.StartupParameters
    if ($servername -eq 'sqlcluster') {
        $startparams = "-dM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf;-eM:\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
    }
    $result = Restore-SQLMasterDB $server $sqlservice $master $startparams

    # IF RESTORE OF MASTER WENT SUCCESSFULLY
    if ($result) {
        $sql = "ALTER DATABASE msdb MODIFY FILE (NAME=MSDBData, FILENAME= '$systemdir\MSDBData.mdf');
            ALTER DATABASE msdb MODIFY FILE (NAME=MSDBLog, FILENAME= '$systemdir\MSDBLog.ldf');
            ALTER DATABASE model MODIFY FILE (NAME=modeldev, FILENAME= '$systemdir\model.mdf');
            ALTER DATABASE model MODIFY FILE (NAME=modellog, FILENAME= '$systemdir\modellog.ldf');
            ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME= '$systemdir\tempdb.mdf');
            ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME= '$systemdir\templog.ldf');"
        Write-Host "Master successfully restored! Restarting SQL Server." -ForegroundColor Green
        Start-Sleep 5
    } else {
        Write-Host "Master could not be master restored." -ForegroundColor Red
        Write-Host "Aborting system db restores and starting SQL Server in normal mode." -ForegroundColor Red
        $sqlservice.StartupParameters = $startparams
        try { Stop-SQLservice $sqlservice; Start-SQLservice $sqlservice } catch {}
        return $false
}

    Write-Host "Resetting startup to normal, and updating msdb, model and tempdb locations." -ForegroundColor Yellow
    $timeout = new-timespan -Minutes 2
    $sw = [diagnostics.stopwatch]::StartNew()

    do {    
        try {
            $server.ConnectionContext.Connect()
            $server.databases['master'].ExecuteNonQuery($sql)
            Write-Host "Master successfully updated with new system db locations! Restarting SQL Server." -ForegroundColor Green
            $success = $true
        } catch { 
            write-warning "Connect failed, retrying.."; 
            if ($server.isclustered) {
                if ($remotesqlservice.Status -ne "Stopped") { Stop-SQLservice $remotesqlservice $true }
                Start-SQLservice $remotesqlservice $true 
            } else {
                if ($sqlservice.ServiceState -ne "Stopped") { Stop-SQLservice $sqlservice }
                Start-SQLservice $sqlservice
            }
        }
    } until ($success -eq $true -or $sw.elapsed -gt $timeout) 

    $sqlservice.StartupParameters = $startparams; $sqlservice.Alter()

    if ($server.isclustered) {
        Stop-SQLService $remotesqlservice $true
    } else { Stop-SQLservice $sqlservice }

    Start-SQLservice $sqlservice
    Write-Warning "Restoring msdb"
    Restore-SQLSystemDatabase $server "msdb" $msdb $systemdir
    Write-Warning "Restoring model"
    Restore-SQLSystemDatabase $server "model" $model $systemdir
    $sqlagent.start()

    try { $server.databases.refresh()
            foreach ($db in $server.databases) {
                if ($db.status -ne "Normal") { 
                    try { $dbname = $db.name
                        $sql = "ALTER DATABASE [$dbname] SET EMERGENCY; drop database [$dbname]"
                        $server.databases['master'].ExecuteNonQuery($sql)
                    } catch {}
                }
            } 
    } catch {}
    Remove-Item $currentmasterbackup
    Write-Host "Systems databases successfully restored! Moving on to user databases.." -ForegroundColor Green
    return $true
}

Function Start-SQLService {
 <#
    .SYNOPSIS
    Restarting SQL Server via SQL's WMI objects returns $true too quickly. This function waits until the SQL service has started or a timeout occurs.
    .EXAMPLE
    $result = Start-SQLService $sqlservice $clustered

    .OUTPUTS
      true or false

 #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$service,

            [Parameter(Mandatory = $false)]
            [bool]$clustered    
        )

    $timeout = new-timespan -Seconds 30
    $sw = [diagnostics.stopwatch]::StartNew()

    if ($clustered) {
        do {
            $service.Refresh()
            try { 
                $service.Start() 
                Write-Warning "Starting SQL.." } 
            catch { Write-Warning "Having trouble starting the SQL service. Let's try again." }

            Start-Sleep 2
        } 
        until ($service.Status -eq "Running" -or $sw.elapsed -gt $timeout) 
        $status = $service.Status   
    } else {
        do {
            $service.Refresh()
            try { 
                $service.Start()
                Write-Warning "Starting SQL.." } 
            catch { 
                Write-Warning "Having trouble starting the SQL service. Let's try again." }
                Start-Sleep 2
        } 
        until ($service.ServiceState -eq "Running" -or $sw.elapsed -gt $timeout) 
        $status = $service.ServiceState
    }

    if ($status -eq "Running") { return $true }
    else { return $false }
}

Function Stop-SQLService {
 <#
    .SYNOPSIS
    Restarting SQL Server via SQL's WMI objects returns $true too quickly. This function waits until the SQL service has stopped or a timeout occurs.
    .EXAMPLE
    $result = Stop-SQLService $sqlservice

    .OUTPUTS
      true or false

 #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$service,

            [Parameter(Mandatory = $false)]
            [bool]$clustered    
        )

    $timeout = new-timespan -Seconds 30
    $sw = [diagnostics.stopwatch]::StartNew()

    if ($clustered) {
        do {
            $service.Refresh()
            try {
                $service.Stop() 
                Write-Warning "Stopping SQL.."
            } catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }

            Start-Sleep 2
        } 
        until ($service.Status -eq "Stopped" -or $sw.elapsed -gt $timeout) 
        $status = $service.Status   
    } else {
        do {
            $service.Refresh()
            try { 
                $service.Stop() 
                Write-Warning "Stopping SQL.."
            } catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }

            Start-Sleep 2
        } 
        until ($service.ServiceState -eq "Stopped" -or $sw.elapsed -gt $timeout) 
        $status = $service.ServiceState
    }

    if ($status -eq "Stopped") { return $true }
    else { return $false }
}

Function Drop-SQLDatabase {
 <#
            .SYNOPSIS
             Uses SMO's KillDatabase to drop all user connections then drop a database. $server is
             an SMO server object.
            .EXAMPLE
              Drop-SQLDatabase $server $dbname
            .OUTPUTS
                $true if success
                $false if failure

        #>
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [object]$server,

            [Parameter(Mandatory = $true)]
            [ValidateNotNullOrEmpty()]
            [string]$dbname
        )

    try {
        $server.KillDatabase($dbname)
        $server.refresh()
        Write-Host "Successfully dropped $dbname on $($server.name)." -ForegroundColor Green
        return $true
    }
    catch { return $false }
}

[Stuart-Moore]

I'll take a look.

When's v1.0 meant to be out the door?

[potatoqualitee]

Merci beaucoup! June 1 I'm hoping

[Stuart-Moore]

Ah, good. For some reason I though it was earlier.

Thinking this through, I think we can break this down into a number of smaller functions that will have some other use:

Set-DbaSqlStartupFlags Start-DbaSqlService Stop-DbaSqlService Start/Stop-DbaClusterInstance

and then the restore is just going to be reusing the existing code.

[Stuart-Moore]

Ready for testing

[Stuart-Moore]

I've got this working well with standalone instances. Clusters need more testing.

Would you be happy for me to push this up for others to test? Would add a check for cluster to warn user and force them accept the risk for now?

[ghost]

are ever going to see Restore-DbaSystemDatabase in dbatools?

[wsmelton]

It is on the roadmap, but not exact time as to when it will be added.

[Stuart-Moore]

Taken my name off of this one as handing someone a resume generating event speedup tool doesn't sit well with me. Trying to get this to work reliably wasn't happening, and I'd kill clustered instances on a regular basis. I would not want to be supporting this AT ALL. This doesn't work the instance is dead in the water and it's a manual fix, which is the same as doing the restore, so might as well just do it the right way in the first place :)

[potatoqualitee]

im gonna go with stuart's recommendation 👍