Skip to content

Instantly share code, notes, and snippets.

@potatoqualitee
Created August 26, 2022 09:08
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save potatoqualitee/c44b0bf248f4a74944c0f94275e7d66b to your computer and use it in GitHub Desktop.
Save potatoqualitee/c44b0bf248f4a74944c0f94275e7d66b to your computer and use it in GitHub Desktop.
dbatools presentation scripts
<#
Combo commands that are powerful and fun
1. Encrypting
2. Testing your backups
3. Disaster Recovery
#>
# Show SSMS
# the password is dbatools.IO
$cred = Get-Credential sqladmin
# fix backups for keys and certs
# add wait option for encryption
# nowait cannot on decrypt because it cant remove that key
# add master key to export
# Introduce TDE and its 10 step process
1..5 | ForEach-Object { New-DbaDatabase -SqlInstance mssql1 -SqlCredential $cred -Name "testdb$PSItem" }
$params2 = @{
SqlInstance = "mssql1"
SqlCredential = $cred
MasterKeySecurePassword = $cred.Password
BackupSecurePassword = $cred.Password
BackupPath = "/shared"
AllUserDatabases = $true
}
Start-DbaDbEncryption @params2 -Confirm:$false
# See backups
Get-DbaFile -SqlInstance mssql1 -SqlCredential $cred -Path /shared | Out-GridView
# See encrypted databases
Get-DbaDatabase -SqlInstance mssql1 -Encrypted | Out-GridView
# setup a powershell splat
$params = @{
Primary = "mssql1"
PrimarySqlCredential = $cred
Secondary = "mssql2"
SecondarySqlCredential = $cred
Name = "test-ag"
Database = "pubs"
ClusterType = "None"
SeedingMode = "Automatic"
FailoverMode = "Manual"
ConnectionModeInSecondaryRole = "AllowAllConnections"
Confirm = $false
}
New-DbaAvailabilityGroup @params
# Show in SSMS then stop
Stop-DbaDbEncryption -SqlInstance mssql1 -SqlCredential $cred
# Backup - introduce during process
Backup-DbaDatabase -SqlInstance mssql1 -SqlCredential $cred
Test-DbaLastBackup -SqlInstance mssql1 -SqlCredential $cred -ExcludeDatabase master
# Wraps like 20
Export-DbaInstance -SqlInstance mssql1 -SqlCredential $cred -Path C:\temp\dr -Exclude ExtendedEvents
Get-ChildItem -Path C:\temp\dr -Recurse -Filter *databa* | Invoke-Item
<#
QUICK OVERVIEW
#>
# agent -> dbatools.io/agent
Get-DbaRegisteredServer | Find-DbaAgentJob -Failed | Get-DbaAgentJobHistory
Get-DbaRegisteredServer | Get-DbaRunningJob
Get-DbaRegisteredServer | Install-DbaMaintenanceSolution
# Scan the instances to check what version & Service Pack/Cumulative Update level we're at
Start-Process https://dbatools.io/builds
Test-DbaBuild -SqlInstance sqlcs, sqlcluster -Latest
# Install-DbaInstance / Update-DbaInstance
Update-DbaInstance -ComputerName sql2017 -Path \\dc\share\patch -Credential base\ctrlb
Invoke-Item 'C:\temp\psconf\Patch several SQL Servers at once using Update-DbaInstance by Kirill Kravtsov.mp4'
# CSV galore!
Get-ChildItem C:\temp\psconf\csv
Get-ChildItem C:\temp\psconf\csv | Import-DbaCsv -SqlInstance sqlcs -Database tempdb -AutoCreateTable
Invoke-DbaQuery -SqlInstance sqlcs -Database tempdb -Query "Select top 10 * from [jmfh-year]"
# Process
Get-DbaProcess -SqlInstance sqlcs -Database tempdb |
Out-GridView -PassThru |
Stop-DbaProcess
# Network and Firewall
Get-DbaFirewallRule -SqlInstance sql01
New-DbaFirewallRule -SqlInstance sql01
# Need help with OSS adoption?
Start-Process https://dbatools.io/secure
# Visit our command page
Start-Process https://dbatools.io/commands
break
<#
01. Find instances
02. Connect to instances
03. Check backups
04. Check disk space
05. Perform backups
06. Check for corruption
07. Install maintenance scripts
08. Export all settings for DR
09. Look for failed jobs
10. Check a few settings (memory & power plan)
11. See what SQL components are installed
12. Discover service accounts
13. Update SQL Server
14. Do a migration
15. Langiappe
Let's look around SQL Server Management Studio first
#>
# First, find unknown instances
# Probes tcp, udp, spns, wmi, all sorts of stuff
Find-DbaInstance -ComputerName macmini, sqlcs, sql2000 | Select-Object * | Out-GridView
# Next, try to connect!
Connect-DbaInstance -SqlInstance sql2000, sqlcs | Get-Member
$cred = Get-Credential sqladmin
Connect-DbaInstance -SqlInstance sqlcs -SqlCredential $cred
# sql logins vs windows + requires windows admin
Reset-DbaAdmin -SqlInstance sqlcs -SqlCredential $cred
Connect-DbaInstance -SqlInstance sqlcs -SqlCredential $cred
# Check your backups
Get-DbaLastBackup -SqlInstance sqlcs | Select-Object * | Out-GridView
# Backups missing? Check disk space to see if you can
Get-DbaDiskSpace -ComputerName sqlcs
Get-DbaDiskSpace -ComputerName sql01, sql02 | Out-GridView
# Cool, hurry and backup your whole instance real quick
Backup-DbaDatabase -SqlInstance sqlcs -Compress
Backup-DbaDatabase -SqlInstance sqlcs -Compress -Type Log
# Confirm the backups
Get-DbaLastBackup -SqlInstance sqlcs | Out-GridView
# Also check to see if your databases have been checked for corruption
Get-DbaLastGoodCheckDb -SqlInstance sqlcs | Out-GridView
# Install Ola's scripts
# Talk about importance of logs
# and scheduling and permissions
$params = @{
SqlInstance = "sqlcs"
InstallJobs = $true
ReplaceExisting = $true
BackupLocation = "\\san\sql\backups"
}
Install-DbaMaintenanceSolution @params
# Run some backups
New-DbaDatabase -SqlInstance sqlcs
Get-DbaAgentJob -SqlInstance sqlcs | Out-GridView -PassThru | Start-DbaAgentJob -Wait
Invoke-Item -Path \\san\sql\backups
# if you're curious
Start-DbaAgentJob -SqlInstance sqlcs -Job "DatabaseBackup - USER_DATABASES - FULL"
Get-DbaRunningJob -SqlInstance sqlcs
# Go schedule then disable VSS
Get-Service -ComputerName sqlcs -DisplayName "SQL Server VSS Writer" | Set-Service -Status Stopped -StartupType Disabled
Test-DbaLastBackup -SqlInstance sqlcs | Out-GridView
# Wraps like 20
Export-DbaInstance -SqlInstance sqlcs -Path C:\temp\dr
Get-ChildItem -Path C:\temp\dr -Recurse -Filter *databa* | Invoke-Item
# agent -> dbatools.io/agent
Find-DbaAgentJob -SqlInstance sqlcs -Failed | Get-DbaAgentJobHistory
<#
To see why a job failed, you can also open up SSMS and expand the job step.
SSMS -> Connect to Server -> SQL Agent Job -> Right click -> View history
#>
# fix max memory and powerplan
Test-DbaMaxMemory -SqlInstance workstation -SqlCredential $cred | Set-DbaMaxMemory -WhatIf
Test-DbaPowerPlan -ComputerName sqlcs | Set-DbaPowerPlan -WhatIf
# Good for any system
Get-DbaOperatingSystem -ComputerName sqlcs | Out-GridView
Get-DbaComputerSystem -ComputerName sqlcs | Out-GridView
# Tip: Install only what you need
Get-DbaFeature -ComputerName sqlcs
# TIP ABOUT SQL SERVICE ACCOUNTS
Get-DbaService -ComputerName sqlcs | Out-GridView
Get-DbaService -ComputerName sqlcs | Select-Object * | Out-GridView
Get-DbaService -ComputerName sqlcs -Instance MSSQLSERVER -Type Agent | Update-DbaServiceAccount -Username 'Local system' -Verbose
# Scan the instances to check what version & Service Pack/Cumulative Update level we're at
Test-DbaBuild -SqlInstance sqlcs, sqlcluster -Latest
# Install-DbaInstance / Update-DbaInstance
Update-DbaInstance -ComputerName sql2017 -Path \\dc\share\patch -Credential base\ctrlb
Invoke-Item "$home\OneDrive\syseng\Patch several SQL Servers at once using Update-DbaInstance by Kirill Kravtsov.mp4"
# Perform the database and accompanying login migration
Copy-DbaDatabase -Source sqlcs -Destination sqlcluster -Database AdventureWorks -BackupRestore -SharedPath \\san\sql\migration -Force
Copy-DbaLogin -Source sqlcs -Destination sqlcluster -Login ad\sqldba -Force
# Langiappe
# docker - dbatools.io/docker & dbatools.io/docker-repo
# multiarch docker images based on sql server 2019
Start-Process https://dbatools.io/docker
# Show Reg Servers in SSMS
Get-DbaRegisteredServer
Get-DbaRegisteredServer -Name mssql1 | Connect-DbaInstance
# dbatools has its own tools -> options
Get-DbatoolsConfig | Out-Gridview
# Find detached databases, by example
1..5 | ForEach-Object {
New-DbaDatabase -SqlInstance sqlcs | Detach-DbaDatabase
}
Find-DbaOrphanedFile -SqlInstance sqlcs | Out-GridView -PassThru |
Select-Object -ExpandProperty RemoteFilename | Get-ChildItem -OutVariable del
$del | Remove-Item
$del | Get-ChildItem
# CSV galore!
Get-ChildItem $home\OneDrive\syseng\csv
Get-ChildItem $home\OneDrive\syseng\csv | Import-DbaCsv -SqlInstance sqlcs -Database tempdb -AutoCreateTable
Invoke-DbaQuery -SqlInstance sqlcs -Database tempdb -Query "Select top 10 * from [jmfh-year]"
# Process
Get-DbaProcess -SqlInstance sqlcs -Database tempdb |
Out-GridView -PassThru |
Stop-DbaProcess
# Network and Firewall
Get-DbaFirewallRule -SqlInstance sql01
New-DbaFirewallRule -SqlInstance sql01
# Got here? Show them TDE!
$cred = Get-Credential sqladmin
$params = @{
SqlInstance = "localhost"
SqlCredential = $cred
MasterKeySecurePassword = $cred.Password
BackupSecurePassword = $cred.Password
BackupPath = "/tmp"
AllUserDatabases = $true
}
Start-DbaDbEncryption @params
Get-DbaFile -SqlInstance localhost -SqlCredential $cred -Path /tmp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment