A test of using notion to publish a quick cheatsheet/reference guide instead of managing through gists.

Get Dbatools

<aside> 💡 This is only required because I needed it to work on a system that had no PowerShell 5+, PowerShellGet, or anything else that simplified the install.

</aside>

## Get dbatools for when even the install scripts fail
# View releases here: <https://github.com/sqlcollaborative/dbatools/releases>
$ToolsDir = 'C:\\tools'
try
{
    # This failed on all powershell 4.0. instance so alternative approach is used
    Invoke-Expression (Invoke-WebRequest -UseBasicParsing <https://dbatools.io/in>).Content
}
catch
{
    Write-Warning 'Failed to download using IEX, so downloading zip and extracting manually'
    $OutZip = Join-Path $ToolsDir 'dbatools.zip'
    Invoke-WebRequest -Uri '<https://github.com/sqlcollaborative/dbatools/archive/1.0.5.zip>' -UseBasicParsing -OutFile $OutZip
    Add-Type -assembly 'system.io.compression.filesystem'
    [io.compression.zipfile]::ExtractToDirectory($OutZip, $ToolsDir) # true is for overwrite
    $Extracted = (Get-ChildItem $ToolsDir | Where-Object Name -Match 'dbatools-\\d').FullName
    $UserModulePath = (Join-Path $ENV:USERPROFILE 'Documents\\WindowsPowerShell\\Module')
    New-Item (Join-Path $ENV:USERPROFILE 'Documents\\WindowsPowerShell\\Module') -Force -ItemType Directory
    Move-Item $Extracted -Destination (Join-Path $UserModulePath 'dbatools') -Force
    Import-Module (Join-Path $UserModulePath 'dbatools')
}

Restoring

The restore is valid even on newer versions (I use for container database restores).

Look at the dbatools docs for the command and you can restore a full directory of backups, do point in time and more. It's just so much easier to do this with dbatools than using SSMS or trying to use dynamic sql to script this out.

###################
# Get Credentials #
###################

$SqlLogin = ''
#Not 4.0 compatible $cred = [pscredential]::new($SqlLogin, ($Pass | ConvertTo-SecureString -Force -AsPlainText))
$cred = Get-Credential -UserName $SqlLogin -Message 'Enter sql credential password'

#########################################################################################################
# Setup Restore for Database 
# Could modify to remove database name and restore a whole server this way)
#########################################################################################################
$Database = ''
$Directory = "C:\\temp\\backups-to-restore-$(Get-Date -Format 'yyyy-MM-dd')\\$Database"
New-Item $Directory -ItemType Directory -Force -ErrorAction SilentlyContinue

# Using DBATOOLS

$restoreSqlDatabaseSplat = @{
    SqlInstance                      = 'localhost'
    Database                         = $Database
    Path                             = $Directory
    SqlCredential                    = $cred
    ReplaceDbNameInFile              = $true
    UseDestinationDefaultDirectories = $true
    WithReplace                      = $false
    WhatIf                           = $true #safety net to preview results
    OutputScriptOnly                 = $true #safety net to preview results

}

Restore-DbaDatabase @restoreSqlDatabaseSplat

Set Default Backup Path Using Dbatools

$TargetNewDirectory = 'X:\\BACKUP'
$script:SqlLoginName = ''
$script:SqlPassword = Read-Host -AsSecureString
$script:SqlCredential = [pscredential]::new($script:SqlLoginName, $script:SqlPassword)
Write-Host "Current Information`n$(Get-DbaDefaultPath -SqlInstance localhost -SqlCredential $script:SqlCredential | Format-List -Force | Out-String)"
$conn = Connect-DbaInstance -SqlInstance localhost -SqlCredential $script:SqlCredential
Write-Host "Backup Directory Information`n$($conn.Properties['BackupDirectory'] | Format-List | Out-String)"
if (Test-Path $TargetNewDirectory -PathType Container)
{
    $conn.Properties['BackupDirectory'].Value = $TargetNewDirectory
    $conn.Alter()
}