index
Symbols
$datatable variable 72
$instances variable 60
$options object 186
$securepass variable 324
$server object 351
A
Action=RunDiscovery flag 92
Active Directory
finding nested Active Directory Group access 131 – 132
finding SQL Server instances on network 85 – 86
AdminAccount 171
ADS (Azure Data Studio) 33, 45, 105 – 107, 183, 287, 350
AgentCredential 169
AGs (availability groups) 222 – 228
creating 222 – 224
based on containers 224
based on WSFC 222 – 224
exploring existing 225 – 227
managing existing 227 – 228
All 87
AllDatabases switch 196
Allowed value 317
ALTER INDEX statement 335
ALTER PROCEDURE statement 159
AutoCreateTable 67
AvailabilityGroup 226
Azure
Az.Accounts 353
backups 137 – 139
access keys 138 – 139
shared access signatures 137 – 138
connecting to 350 – 352
restores 154 – 155
access keys 154 – 155
shared access signatures 154
service principals and access tokens 352 – 353
writing results of commands to 74 – 75
AzureCredential 154
B
backup compression 329
Backup-Dbadatabase 325
BackupRestore 196 – 197
backups
creating 134 – 140
Azure 137 – 139
Docker 139 – 140
encryption 323 – 326
backing up database with certificate 325
checking encryption information from backup 325 – 326
prerequisites for 324 – 325
history 141 – 142
instance migration 195 – 197
pruning old files 142
reading files 140 – 141
testing 142 – 144
C
catch block 242
Category 252
CategoryType 263
Central Management Server. See CMS
certificates 311 – 315
listing of certificates that SQL Server can use 313
obtaining and installing 312 – 313
setting 313 – 315
Check 341
ChecksResultDBName value 348
Chocolatey 22
CI/CD systems, running dbatools and PowerShell on 294 – 297
adding parameters to script 295 – 297
creating tasks 295
ensuring module is available 295
clip 62
cloud database services
Az.Accounts 353
connecting to Azure 350 – 352
future of 355
service principals and access tokens 352 – 353
supported 353 – 355
CmdExec
creating jobs with 238 – 240
PowerShell Agent job steps vs. 231 – 232
CMS (Central Management Server)
getting Registered Servers in both Local Server Groups and 111
overview 107 – 109
Column 279
columnstore compression 329
CompressBackup 135, 329
CompressionType 337
ComputerName 10, 54 – 56, 93, 220
Config property 344
Configuration 171 – 173
configuration system 356 – 359
changing values 359
checking existing configurations 356 – 357
getting specific configurations 357 – 358
getting values 358 – 359
importing configurations 359
resetting to default values 359
ConfigurationFile 171 – 173
ConfigurationFile.ini template 165
Connect-DbaInstance 37, 50
ConnectedAs property 51
ConnectSuccess property 43
containers
creating availability groups based on 224
demo environments using 35 – 39
overview 39
running SQL Server in 36 – 39
Continue 151
Convert-DbcResult 347
ConvertTo-DbaDataTable 72
ConvertTo-DbaXESession 300
Copy 210
Copy commands
Copy-DbaAgentAlert 206
Copy-DbaAgentJob 211
Copy-DbaAgentOperator 205
Copy-DbaCredential 213
Copy-DbaCustomError 206
Copy-DbaDatabase 195
Copy-DbaDbTableData 76, 155
Copy-DbaLinkedServer 213
Copy-DbaLogin 207
Copy-DbaRegServer 109
Copy-DbaXESession 302
COPY_ONLY flag 199
Credential 55 – 56, 58, 220
listing services
by type 58
using different account at command line 56
using different account with credential variables 57
CSR (Certificate Signing Request) 312
CSV files, importing from 64 – 68
creating table first 70 – 73
Import-Csv with Write-DbaDataTable 66 – 68
Import-DbaCsv 64 – 65
D
DAC (dedicated administrative connection) 213
DACPAC 287 – 294
exporting from existing database 287 – 289
options 290 – 294
publishing 289 – 290
data compression
advanced settings 338 – 339
dbatools 336 – 337
drawbacks of 330 – 331
good candidates for 333
reasons for using 330
rowstore data compression 329 – 330
specifying compression level 337 – 338
types of 328 – 329
what is compressed 331 – 332
what should be compressed 332 – 335
data masking
common approach 275 – 276
generating random data 276 – 277
process for 277 – 283
applying static data masking 282 – 283
defining deterministic columns 281 – 282
finding potential PII data 277 – 279
generating configuration file 279 – 282
validating data masking configuration file 283
Database 97, 135, 252
DatabaseName 151
DataPath 171
datatable object 72
Datatype 277
DateTime 254
datetime format 152
dba operator 211
dbachecks
compared to dbatools 340 – 341
configuring parameters 343 – 346
making checks 341 – 343
storing output data in databases 346 – 348
configuring connection 348
Power BI dashboard 348
storing data 347 – 348
viewing all available checks 343
dbatools
docs.dbatools.io 25
Extended Events 302
help system 23 – 24
installing
installation paths 15 – 16
minimum requirements 8 – 13
signed software 13 – 14
via Chocolatey 22
via PowerShell Gallery 16 – 21
via zipped archive 21 – 22
logging system 360
exploring activity 360 – 361
ongoing logging 360 – 361
purpose of book 5 – 6
running on CI/CD system 294 – 297
adding parameters to script 295 – 297
creating tasks 295
ensuring module is available 295
shared access signatures 4 – 5
updating 25 – 26
via alternative methods 26
via PowerShell Gallery 25 – 26
when to use in DevOps 286
dbatools commands
checking SQL connections 41
ComputerName 54 – 56
Credential 56 – 58
listing services using different account at command line 56
listing services using different account with credential variables 57
listing SQL services by type 58
EnableException 59
finding 23 – 25
Find-DbaCommand 23
Get-Command 23
Get-Help 41 – 43
importing from 68 – 70
running 43 – 45
SqlCredential 48 – 53
connecting to instances with different Windows accounts 52 – 53
SQL Server Authentication 49 – 52
SqlInstance 45 – 48
multiple instances 46 – 48
single instances 45 – 46
writing results of other commands to Azure SQL Database 74 – 75
writing results of other commands to tables 73
dbatools lab
demo environments using containers 35 – 39
options for building 28
reason for 27 – 28
Windows lab 29 – 35
building 30 – 32
configuration scripts 33 – 34
installation media 29
dedicated administrative connection (DAC) 213
Desired State Configuration (DSC) 164
Destination 192
DestinationDataDirectory 150
DestinationLogDirectory 150
detach and attach migration option 197 – 198
DetachAttach 198
DetachAttach switch 197
Deterministic property 281
Deterministic value 281
DevOps automation
DACPAC 287 – 294
exporting from existing database 287 – 289
options 290 – 294
publishing 289 – 290
running dbatools and PowerShell on CI/CD system 294 – 297
adding parameters to script 295 – 297
creating tasks 295
ensuring module is available 295
when to use dbatools in DevOps 286
Disable-DbaDbEncryption 322
disaster recovery
availability groups 222 – 228
creating 222 – 224
exploring existing 225 – 227
managing existing 227 – 228
exporting entire instance 180 – 187
excluding objects 187
scripting options 184 – 185
setting scripting options 186
exporting server configurations 191 – 192
granular exports 187 – 190
log shipping 215 – 219
configuring 216 – 217
gathering errors 217 – 218
secondary databases 218 – 219
special commands 190 – 191
WSFC 219 – 221
discovery types 81
docker run 37
Docker, creating backups 139 – 140
docs.dbatools.io 25
Domain discovery type 85
DSC (Desired State Configuration) 164
E
EKM (Extensible Key Management) 320
Enable-DbaAgHadr 222
EnableException 59, 242, 361
Encrypted 321
encryption
authentication 316 – 318
database backups encryption 323 – 326
backing up database with certificate 325
checking encryption information from backup 325 – 326
prerequisites for 324 – 325
hiding instances 318 – 319
network connections 310 – 316
certificates 311 – 315
force encryption 315 – 316
transparent data encryption 319 – 322
decrypting databases 321 – 322
encrypting databases 319 – 321
EncryptionAlgorithm 325
EndDate 259
EngineCredential 169
Enter-PSSession 12
Errors 361
event_file type 308
Exclude 187
ExcludeDatabase 332
ExcludeLogin 208
ExcludeObjectTypes option 291
ExcludeSystem switch 98
ExcludeUser switch 98
Export commands
Export-DbaDacPackage 281 – 283
Export-DbaInstance 181
Export-DbaLogin 128
Export-DbaRegServer 110
Export-DbaScript 188 – 190
Export-DbaSpConfigure 191
Export-DbatoolsConfig 359
Extended Events 301 – 308
cleanup 308
converting traces to 300 – 301
dbatools support 302
finding 302 – 303
reading data 306 – 308
postmortem analysis 308
watching live data 307 – 308
replicating sessions to multiple instances 308
SSMS support 301 – 302
starting and stopping sessions 305 – 306
templates 303 – 305
Extensible Key Management (EKM) 320
F
FailoverCluster module 219
Fallback 359
File 239
Find-DbaAgentJob 256
Find-DbaCommand 23, 251, 261
Flag 270
Force 52, 208, 227
force encryption 315 – 316
FrequencySubdayInterval 264
FrequencySubdayType 264
FTCredential 169
Full 126
FullName 359
FullName property 359
G
GCP (Google Cloud Platform) 350
Get commands 225
Get-AzVM 74
Get-Command 23, 214, 251, 261
Get-Content 63
Get-Credential 51, 324
Get-DbaAgentJob 251
Get-DbaAgentJob-Category 252
Get-DbaAgentJobHistory 273
Get-DbaComputer-Certificate 313
Get-DbaDatabase
filtering results returned by last backup time 98 – 100
filtering results returned from 97 – 98
Get-DbaDbCompression 332
Get-DBaDbOrphanUser 123
Get-DbaDbTable 68
Get-DbaDiskSpace 9
Get-DbaErrorLog 64
Get-DbaFeature 93
Get-DbaOperatingSystem 96
Get-DbaRandomizedType 277
Get-DbaRandomized-Value 277
Get-DbaRegServer 112
Get-DbaRunningJob 272
Get-DbaService 55
Get-DbatoolsConfig 356
Get-DbaWsfcResource 221
Get-Help 23 – 24, 41 – 43
Get-Member 70, 190
H
HADR (high availability and disaster recovery)
availability groups 222 – 228
creating 222 – 224
exploring existing 225 – 227
managing existing 227 – 228
log shipping 215 – 219
configuring 216 – 217
gathering errors 217 – 218
secondary databases 218 – 219
WSFC 219 – 221
help resources
docs.dbatools.io 25
Get-Help 23 – 24, 41 – 43
Hicks, Jeff 7
Holmes, Lee 364
I
IAC (Infrastructure as Code) 124, 285
IF NOT EXISTS statement 186, 189
IFI (instant file initialization) 320
Import commands
Import-Csv 66 – 68
Import-DbaCsv 64 – 65
Import-DbaRegServer 102, 110
Import-DbaSpConfigure 185 – 186
Import-DbatoolsConfig 359
Import-DbaXESessionTemplate 304
Import-Module 16
IncludeIfNotExists property 186
IncludeSelf 114
Infrastructure as Code (IAC) 124, 285
Install-DbaInstance 165
installing dbatools
installation paths 15 – 16
minimum requirements 8 – 13
execution policy 12 – 13
ports 10 – 12
server 9
workstation 9 – 10
signed software 13 – 14
via Chocolatey 22
via PowerShell Gallery 16 – 21
all users 18
local user 19
offline install 20 – 21
overview 16 – 17
trusting PowerShell Gallery 17
via zipped archive 21 – 22
invalidated digital signature 22
offline install 22
renaming directory 22
InstanceName 58, 177
instant file initialization (IFI) 320
Invoke commands
Invoke-Command 12
Invoke-DbaDbDataMasking 282
Invoke-DbaDbLogShipRecovery 218
Invoke-DbaQuery 143
Invoke-DbcCheck 341
IpAddress 87
J
Job 210
Jones, Don 7
L
LastError 361
LastFullBackup value 341
LastRaised property 254
Learn PowerShell in a Month of Lunches (Jones and Hicks) 7
linked servers 213 – 214
Local Server Groups 104 – 105
getting Registered Servers in both CMS and 111
version-specific RegSrvr.xml files 105
log shipping 215 – 219
configuring 216 – 217
gathering errors 217 – 218
secondary databases 218 – 219
Login Tracker Extended Event template 209
logins
failed logins 120 – 124
finding nested Active Directory Group access 131 – 132
identifying how access was gained 128 – 132
preventing issues 124 – 126
source control 126 – 128
LogPath 171
M
MAPS (Microsoft Assessment and Planning Toolkit) 81
Message property 259
Microsoft.PowerShell.GraphicalTools module 170
migration, SQL Server instance 194 – 214
MinimumConfidence 84
Module 357
Module switch 261
MSX (master servers) 262
N
Name 159, 305
New commands
New-DbaAgentSchedule 266
New-DbaAvailability-Group 223
New-DbaAzAccessToken 352
New-DbaComputer-Certificate 312
New-DbaDacOption 291
New-DbaDbCertificate 324
New-DbaDbMasking-Config 279
New-DbaDbSnapshot 162
New-DbaLogin 120
New-DbaScriptingOption 184
New-DbaXESession 305
NoClobber 246
NoCopyOnly 203
NoFullBackup 98
nonrecovered databases 150 – 151
NoRecovery 150, 199
NotNull 359
NoTypeInformation 63
Noun switch 261
NT SERVICE service accounts 173
NumberFiles 196
O
Off value 317
OnFailAction 269
Online 25
OnSuccessAction 269
Out-GridView 170
OutputScriptOnly 136, 149
OutVariable 207
P
PageRestore 153
Param block 296
PassThru 130
Passthru 161, 212
Password property 320
Path 159
PATH variables 15
Payette, Bruce 364
permissions 126 – 128
PII (personally identifiable information) 274, 277 – 279
pipeline 60
point-in-time restores 152
Power BI 348
PowerShell
automation 3 – 4
learning more about 364
running on CI/CD system 294 – 297
adding parameters to script 295 – 297
creating tasks 295
ensuring module is available 295
SQL Server Agent
CmdExec vs. PowerShell job steps 231 – 232
creating jobs to run 232 – 238
ensuring job fails when PowerShell fails 241 – 244
story of SQL Server DBA 2 – 3
why data professionals can't afford to ignore 1 – 3
PowerShell Cookbook (Holmes) 364
PowerShell Desired State Configuration 164
PowerShell Gallery, installing dbatools via 16 – 21
all users 18
local user 19
offline install 20 – 21
overview 16 – 17
trusting PowerShell Gallery 17
PowerShell in Action (Payette and Siddaway) 364
PowerShell splatting 76 – 78
PSCredential object 51 – 52
Publish-DbaDacPackage 289
R
RandomizerSubType 277
RandomizerType 277
RDP (Remote Desktop) 43
RDS (Relational Database Service) 350
Read-DbaXEFile 306
Reattach 198
Recovery Time Objective (RTO) 180
Registered Servers
adding new 112 – 114
Azure Data Studio 105 – 107
Central Management Server 107 – 109
copying 115 – 116
exporting 115 – 116
groups 117 – 118
importing 115 – 116
inventory organization 109 – 111
Local Server Groups 104 – 105
moving 116 – 117
removing 117
RegSrvr.xml files 105
Remove-DbaXESession 308
Repair-DbaDbOrphanUser 124
ReplaceDbNameInFile 151
Required value 317
Reset-DbatoolsConfig 359
restores
Azure 154 – 155
access keys 154 – 155
shared access signatures 154
considerations regarding 145 – 146
instance migration 195 – 197
limitations of 145 – 146
restoring to custom data and log directories 149 – 154
nonrecovered databases 150 – 151
point-in-time restores 152
recovering corrupt databases 153 – 154
renaming databases 151 – 152
restoring to marked transactions 152 – 153
scenarios for 146 – 149
directories 147 – 149
single files 147
T-SQL restore scripts 149
snapshots
cleaning up 162
creating 158 – 159
reporting 162 – 163
restoring certain objects or data from 161
rolling back entire database from 160 – 161
upgrading 159 – 160
when to use 158
RestoreTime 152
rowstore data compression 328 – 330
RTO (Recovery Time Objective) 180
S
SampleCount 279
SASs (shared access signatures) 154
scan types 81
ScriptingOptions 184
Secure Sockets Layer (SSL) 311
security
encryption
authentication 316 – 318
database backups encryption 323 – 326
hiding instances 318 – 319
network connections 310 – 316
transparent data encryption 319 – 322
multilayered security 327
Select-Object 307
Set 153, 337
Set commands
Set-DbaDbCompression 337
Set-DbaExtended-Protection 317
Set-DbaNetwork-Certificate 314
Set-DbaSpConfigure 214
Set-DbaSpn 318
Set-DbatoolsConfig 359
Set-PSRepository 17
SetSourceOffline 197
setspn.exe 85
setup.exe 165
shared access signatures (SASs) 154
Show 345
SID (Security Identifier) 123
Siddaway, Richard 364
signed software 13 – 14
Since 258
SMO (SQL Management Object) 184
snapshots
application upgrade 157 – 158
cleaning up 162
creating 158 – 159
reporting 162 – 163
restoring certain objects or data from 161
rolling back entire database from 160 – 161
SSMS and 156 – 157
upgrading 159 – 160
when to use 158
Source 192
sp_configure values 34
Split 344
SPN (service principal names) 85
SQL Management Object (SMO) 184
SQL Profiler 299 – 301
SQL Server
activity tracing
Extended Events 301 – 308
SQL Server Trace and SQL Profiler 299 – 301
checking connection 41
instances from separate sources 47 – 48
instances stored in variables 47
multiple instances passed as array 46
piping in instance names 47
to single local default instance 46
to single local named instance 46
to single remote default instance 45
to single remote named instance 45
using nondefault port number 48
connecting to instances with different Windows accounts 52 – 53
copying tables 75 – 78
finding instances on network 81 – 88
detailed results 88 – 90
in Active Directory domain 85 – 86
in surrounding network 86 – 88
OS support 90
overview 81
using list of targets 82 – 85
using SQL Server Browser 82 – 85
installing 164 – 175
benefits of automated installs 165 – 166
built-in parameters 173 – 175
ConfigurationFile and Configuration parameters 171 – 173
customizing installation options 171
local installs 167
postinstall actions 175
remote installs 168 – 170
inventorying
build information 94 – 95
databases 96 – 100
feature usage 92 – 93
gathering information into database 100 – 101
host information 95 – 96
Registered Servers 104 – 118
listing services
by type 58
on multiple servers 56
using different account at command line 56
using different account with credential variables 57
patching 176 – 177
fear of breaking everything 176
procrastination 177
piping commands 60 – 64
SQL Server Authentication
connecting to instances with 49 – 50
using credentials for 50 – 52
updating 175 – 176
writing to databases 64 – 75
importing from CSV files 64 – 68, 70 – 73
importing from dbatools commands 68 – 70
writing results of other commands 73 – 75
SQL Server Agent 235 – 236, 253, 255, 264 – 266
alerts 253 – 255
bonus job commands 270 – 273
Get-DbaAgentJob-History 273
Get-DbaRunningJob 272
Start-DbaAgentJob 271
CmdExec vs. PowerShell job steps 231 – 232
instance migration 209 – 212
job creation 232 – 238, 261 – 270
creating categories 262 – 263
creating jobs 266 – 269
creating operators 266
creating proxies 264 – 266
creating schedules 263 – 264
PowerShell file 237 – 238
running files 269 – 270
SQL Server Agent proxies 235 – 236
SQL Server credentials 234 – 235
with CmdExec job step 238 – 240
job history 259 – 260
job results 258 – 259
listing information 250 – 257
finding specific jobs 256 – 257
SQL Server Agent alerts 253 – 255
SQL Server Agent jobs 250 – 252
proxies 235 – 236, 255, 264 – 266
tips for 240 – 248
default parameter values 240
ensuring job fails when PowerShell fails 241 – 244
execution policies 248
logging 244 – 247
SQL Server Browser 82 – 85
SQL Server credentials 234 – 235
SQL Server instance migration
databases 194 – 204
backup and restore 195 – 197
detach and attach 197 – 198
staging large databases 198 – 203
groups 206 – 209
linked servers 213 – 214
logins 206 – 209
SQL Agent objects 209 – 212
SQL Server operators 255, 266
SQL Server SysPrep 165
SQL Server Trace 299 – 301
SQL WMI 11
sqladmin login 186
SqlCredential 37, 48 – 53, 225, 240
connecting to instances with different Windows accounts 52 – 53
SQL Server Authentication 49 – 52
SqlInstance 37, 45 – 48, 60, 106, 109, 263, 319, 348, 352
multiple instances 46 – 48
single instances 45 – 46
SSL (Secure Sockets Layer) 311
SSMS (SQL Server Management Studio) 2, 31, 41, 102, 120, 145, 156, 183, 205, 215, 229, 249, 287, 298, 311, 335, 350
Extended Events 301 – 302
snapshots and 156 – 157
staging large databases for migration 198 – 203
applying changes and bringing destination online 201 – 203
staging full backup 199 – 201
Start-DbaAgentJob 271
Start-DbaDbEncryption 319
Start-DbcPowerBi 348
Start-Transcript 246 – 247
StartDate 259
StartTime 263
Status 306
StepId 269
Stop-DbaAgentJob 272
StopAt 306
T
T-SQL restore scripts 149
Table 279, 337
Tag 23
TDE (transparent data encryption) 319 – 322
decrypting databases 321 – 322
encrypting databases 319 – 321
Test commands
Test-DbaBackup-Encypted 325 – 326
Test-DbaBuild 94 – 95
Test-DbaComputerCertificate-Expiration 313
Test-DbaConnection 24, 43 – 46, 61
Test-DbaDbCompression 333 – 337
Test-DbaDbDataMasking-Config 277
Test-DbaLastBackup 136 – 138, 143
Test-DbaMaxMemory 49
Test-DbaSpn 317 – 318
Throttle 168
Time type 264
TLS (Transport Layer Security) 311
TSX (target servers) 262
Type 58, 177
U
Update 94
users 126 – 128
V
Value 359
Verb switch 261
Version 177
VMs (virtual machines) 35
W
Watch-DbaXESession 306
WHERE clause 159, 258
Where-Object 307 – 308
Windows dbatools lab 29 – 35
building 30 – 32
configuration scripts 33 – 34
installation media 29
WMI (Windows Management Instrumentation) 253
Write-DbaDataTable 66 – 68, 71, 143
WS-Management 12
WSFC (Windows Server Failover Cluster) 219 – 224
