A test of using notion to publish a quick cheatsheet/reference guide instead of managing through gists.
<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')
}
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
$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()
}