Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
dbatools for systems engineers and accidental dbas
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