Created
August 26, 2022 09:08
-
-
Save potatoqualitee/c44b0bf248f4a74944c0f94275e7d66b to your computer and use it in GitHub Desktop.
dbatools presentation scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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