answers.md

answers.md

Something we appreciate about PowerShell and dbatools is that there are often a number of perfectly valid ways to solve the same problem. Considering this, some of the questions have multiple answers.

Some answers are different ways to express the same solution.

Chapter 2

Find all commands that have DbaReg in their name.

Get-Command *dbareg* -Module dbatools
# or
Get-Command *dbareg*
# or
Find-DbaCommand dbareg

Using Get-Help, find examples for the command Install-DbaInstance.

Get-Help Install-DbaInstance -Examples
# or
Get-Help Install-DbaInstance -Detailed

Chapter 4

List the SQL Services on a computer

Get-DbaService -ComputerName server01

List the SQL Services for a specific instance

Get-DbaService -ComputerName server01 -InstanceName SQLEXPRESS

Identify the user account that is running the SQL Agent service

Get-DbaService -ComputerName sql2016 -Type Agent |
    Select-Object ComputerName, InstanceName, StartName

Write a command to return the databases without a log backup

Get-DbaDatabase -SqlInstance sql2017 | Where LastLogBackup -eq '1/1/0001 12:00:00 AM'
# or
Get-DbaDatabase -SqlInstance sql2017 | Where { $_.LastLogBackup.Year -eq 0001 }
# or
Get-DbaDatabase -SqlInstance sql2017 | Where { $PSItem.LastLogBackup.Year -eq 0001 }
# or
Get-DbaDatabase -SqlInstance sql2017 | Where { $PSItem.LastLogBackup -eq 0 }

Write a command to return the databases without a log backup in the last 30 minutes (Make sure your server is in the same timezone)

Get-DbaDatabase -SqlInstance sql2017 | Where { $PSItem.LastLogBackup -ge (Get-Date).AddMinutes(-30) }

Find any databases without a full backup on your test instance

Get-DbaDatabase -SqlInstance sql2017 | Where { $PSItem.LastFullBackup -eq 0 }

Chapter 5

Copy a table from one database to a WIP database on your local instance, using both -AutoCreateTable and a pre-created table

Copy-DbaDbTableData -SqlInstance sql2019 -Destination localhost -Database tempdb -Table myprecreatedtable
# or
Copy-DbaDbTableData -SqlInstance sql2019 -Destination localhost -Database tempdb -Table mynewtable -AutoCreateTable

Gather the SQL logins from a SQL Server to a database table with Get-DbaLogin

Get-DbaLogin -SqlInstance sql2017 | Write-DbaDbTableData -SqlInstance sql2016 -AutoCreateTable -Database tempdb -Table logins
Invoke-DbaQuery -SqlInstance sql2016 -Database tempdb -Query "select * from logins"

Gather the services information into a table with Get-Service

Get-Service -ComputerName sql2017 | Select * | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table services -AutoCreateTable

Gather the file information into a table with Get-ChildItem

Get-ChildItem -File | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table files -AutoCreateTable

Add the information about the SQL Services for an instance into a table

Get-DbaService -ComputerName sql2017 | Select * | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table sqlservices -AutoCreateTable

Find a CSV on your machine and import it into a table

Import-Csv -Path C:\temp\small.csv | Write-DbaDbTableData -SqlInstance sql2016 -Database archive -Table dailyimport
# or
Import-DbaCsv -Path C:\temp\large.csv -SqlInstance sql2017 -Datbase archive -Table dailyimport

Get the information about the tables in a database and import it into a table

$dbs = Get-DbaDatabase -SqlInstance $servers | Select ComputerName, SqlInstance, Name, SizeMB, LastRead, LastWrite
Write-DbaDbTableData -InputObject $dbs -SqlInstance sql2016 -Database tempdb -Table alldbs -AutoCreateTable

Chapter 6

Find SQL Servers in your domain with computers with SQL in the name

Get-ADComputer -Filter "Name -like '*SQL*'" | Find-DbaInstance

Find only Reporting Services

Find-DbaInstance -ComputerName sql2016 | Select -ExpandProperty Services | Where-Object DisplayName -match Report

Find a SQL Server on a non-defaut port (so not 1433) (🐛 fix in 1.0.90)

Find-DbaInstance -ComputerName sql2016 -ScanType TCPPort -TCPPort 49837

Chapter 7

Test compliance of multiple instances against the latest build available

Get-DbaRegServer | Test-DbaBuild -MinimumBuild 12.0.4511
# or
Test-DbaBuild -SqlInstance sql2017, sql2016 -Latest

Search for objects owned by 'sa'

Get-DbaRegServer | Get-DbaDatabase | Where Owner -eq sa
# or
Find-DbaUserObject -SqlInstance sql2017 -Pattern sa

Chapter 8

Add a couple servers to Local Server Groups

Add-DbaRegServer -ServerName sql01
# And with stored credentials!
Connect-DbaInstance -SqlInstance localhost:14333 -SqlCredential sa | Add-DbaRegServer -Description Docker
# and
Add-DbaRegServer -ServerName spsql01 -Name "Primary" -Group SharePoint
Add-DbaRegServer -ServerName spsql02 -Name "Secondary" -Group SharePoint

Find all of the jobs currently running in your estate using Get-DbaRegServer and Get-DbaRunningJob

Get-DbaRegServer -SqlInstance centralsql | Get-DbaRunningJob

Chapter 9

Read the Error Log on your local instance to familiarize yourself with the output

# Find the command you want
Get-Command -Module dbatools *errorlog*
# then
Get-DbaErrorLog -SqlInstance localhost
# or output to gridview for a nice visual
Get-DbaErrorLog -SqlInstance localhost | Out-GridView -Passthru

Create a new SQL login

New-DbaLogin -SqlInstance localhost -Login newlogin

Create a new database user

# First create a database
New-DbaDatabase -SqlInstance localhost -Name test
# Then create the user
New-DbaDbUser -SqlInstance localhost -Database test -Login newlogin

Export the users on your instance to a file

Export-DbaUser -SqlInstance localhost -FilePath C:\temp\dbusers.sql

Set up a local git repository and save the output of Export-DbaLogin

Get all of the user permissions on your instance and export them to an Excel Sheet

Get-DbaUserPermission -SqlInstance localhost | Export-Excel C:\temp\userpermissions.xlsx

Chapter 10

Backup all databases on your SQL Server to a non-default path

Backup-DbaDatabase -SqlInstance sql01 -Path \\nas\sql\backups

Backup a database and pipe the results directly to Read-DbaBackupHeader

Backup-DbaDatabase -SqlInstance sql01 | Read-DbaBackupHeader
# or
Get-DbaDatabase -SqlInstance sql01 -Database master | Backup-DbaDatabase | Read-DbaBackupHeader
# or
Backup-DbaDatabase -SqlInstance sql01 -Database master | Read-DbaBackupHeader

Find transaction log (trn) backups older than 21 days using Find-DbaBackup

# Refresh your brain about the syntax
Get-Help Find-DbaBackup -Examples

# Run the command
Find-DbaBackup -Path \\nas\sql\backups -BackupFileExtension trn -RetentionPeriod 21d

Chapter 11

Backup all user databases in your test SQL Server instance. Perform one full backup, one differential backup, and three log backups then restore the entire folder back to your test instance, ensuring you use -WithReplace.

# Get a list of user databases and set the variable to dbs
Get-DbaDatabase -SqlInstance localhost -UserDbOnly -OutVariable userdbs
# Perform a full backup
$userdbs | Backup-DbaDatabase -Path \\nas\sql\demo
# Perform a diff backup
$userdbs | Backup-DbaDatabase -Path \\nas\sql\demo -Type Diff
# Perform 3 log backups
1..3 | ForEach-Object { $userdbs | Backup-DbaDatabase -Path \\nas\sql\demo -Type Log }

# Perform restore, ensuring that the SQL Server service account has access to the path
Get-ChildItem \\nas\sql\demo | Restore-DbaDatabase -SqlInstance localhost -WithReplace

Restore a single database, and move the data and log files to a new location

Restore-DbaDatabase -SqlInstance sql01 -Path \\nas\sql\demo\full.bak -DestinationDataDirectory D:\Data -DestinationLogDirectory L:\Log

Restore a single database to a new name

Restore-DbaDatabase -SqlInstance workstationx -Path C:\temp\test_202111091442.bak -WithReplace -DatabaseName new
# or, to replace db name in pysical files as well (we kept this separate bc that's how SSMS does it)
Restore-DbaDatabase -SqlInstance workstationx -Path C:\temp\test_202111091442.bak -WithReplace -DatabaseName new -ReplaceDbNameInFile

Chapter 12

Create snapshots for multiple databases at once by using the -NameSuffix parameter

Get-DbaDatabase -SqlInstance localhost -UserDbOnly | New-DbaDbSnapshot -NameSuffix snapz
# or 
New-DbaDbSnapshot -SqlInstance localhost -Database test, new -NameSuffix snapz

Create a snapshot on a different drive than your database data files by using the -Path parameter

New-DbaDbSnapshot -SqlInstance localhost -Database test, new -Path S:\dbsnapshots

Alter some data and then roll it back by using your snapshot

# First, Create a snapshot
New-DbaDbSnapshot -SqlInstance localhost -Database test
# Then alter some data
Get-ChildItem -File | Write-DbaDataTable -SqlInstance localhost -Database test -Table files -AutoCreateTable
# Query for the newly added table
Invoke-DbaQuery -SqlInstance localhost -Database test -Query "select * from files"
# Roll it back using Force in case you still have a connection to the db, using force will kill all connections before performing the restore
Restore-DbaDbSnapshot -SqlInstance localhost -Database test -Force
# Perform a query that will fail because the data doesn't exist anymore, as the database was rolled back
Invoke-DbaQuery -SqlInstance localhost -Database test -Query "select * from files"

Clean up all the snapshots you created ready for the next chapter

Remove-DbaDbSnapshot -SqlInstance localhost -Database test
# or 
Get-DbaDbSnapshot -SqlInstance localhost | Remove-DbaDbSnapshot -Confirm:$false

Chapter 13

Test what would happen if you performed an install using the -WhatIf parameter

Install-DbaInstance -SqlInstance localhost -Path C:\temp -Version 2017 -Feature Default -WhatIf

Do the same with updating a remote instance

Update-DbaInstance -ComputerName sql2017\sqlexpress, server01 -Version CU3 -Download -Path \\network\share -WhatIf

Get some examples for Update-DbaInstance using Get-Help

Get-Help Update-DbaInstance -Examples

Chapter 14

Export the configuration settings for an instance

Export-DbaInstance -SqlInstance localhost
# or to a specific path
Export-DbaInstance -SqlInstance localhost -Path C:\exports

Export all of the configuration settings except for Policy Based Management and Resource Governor for an instance

Export-DbaInstance -SqlInstance localhost -Exclude PolicyManagement, ResourceGovernor

Create T-SQL scripts for Extended Event objects

# look through export commands
Get-Command -Module dbatools *export*
# see the one you want
Get-DbaXESession -SqlInstance localhost | Export-DbaXESession -Path C:\temp

Create T-SQL scripts for the Agent Jobs on an instance

Get-DbaAgentJob -SqlInstance localhost | Export-DbaScript -Path C:\temp

Chapter 15

Migrate the databases from dbatoolslab\SQL2017 to dbatoolslab

Start-DbaMigration -Source dbatoolslab\SQL2017 -Destination dbatoolslab -BackupRestore -SharedPath "\\fileserver\share\sqlbackups\Migration"

Migrate a database with a straightforward method, either backup\restore or detach\attach

Copy-DbaDatabase -Source dbatoolslab\SQL2017 -Destination dbatoolslab -BackupRestore -SharedPath "\\fileserver\share\sqlbackups\Migration"

Migrate a database by staging a full backup, create a new table, then cutover and make sure you can see the new table

Chapter 15

Migrate some of the logins from dbatoolslab\SQL2017 to dbatoolslab

Get-DbaLogin -SqlInstace dbatoolslab\SQL2017 | Out-GridView -Passthru | Copy-DbaLogin -Destination dbatoolslab

Migrate some other objects available on dbatoolslab\SQL2017. For example a SQL Agent job with an operator or a linked server.

# See all the copy commands
Get-Command -Module dbatools *Copy*
# Migrate
Copy-DbaAgentJob -Source dbatoolslab\SQL2017 -Destination dbatoolslab -Job "Full Backups", "Diff Backups", "Log Backups"
Copy-DbaLinkedServer -Source dbatoolslab\SQL2017 -Destination dbatoolslab -LinkedServer SharePointSQL01, SQL02

Connect to the dbatoolslab instance (destination) and confirm everything expected has been migrated successfully

It's fine to do this in SQL Server Management Studio or Azure Data Studio. Sometimes GUI is easiest.

Chapter 17

If you have a WSFC handy run the New-DbaAvailabilityGroup command to set up an availability group

# the password is dbatools.IO
$cred = Get-Credential -UserName sqladmin

# setup a powershell splat
$params = @{
    Primary = "sql01"
    PrimarySqlCredential = $cred
    Secondary = "sql02"
    SecondarySqlCredential = $cred
    Name = "test-ag"
    Database = "pubs"
    ClusterType = "None"
    SeedingMode = "Automatic"
    FailoverMode = "Manual"
    Confirm = $false
 }

# execute the command
 New-DbaAvailabilityGroup @params

Once your AG is setup explore with Get-DbaAgReplica and Get-DbaAgDatabase

Get-DbaAgReplica -SqlInstance sql01, sql02
Get-DbaAgDatabase -SqlInstance sql01, sql02

Cause a failover with Invoke-DbaAgFailover

Invoke-DbaAgFailover -SqlInstance sql01 -AvailabilityGroup test-ag

Suspend data movement and then resume it with Suspend-DbaAgDbDataMovement and Resume-DbaAgDbDataMovement

Get-DbaAgDatabase -SqlInstance sql02 | Out-GridView -Passthru -OutVariable susdb | Suspend-DbaAgDbDataMovement
# or, without prompts
Get-DbaAgDatabase -SqlInstance sql02 | Out-GridView -Passthru -OutVariable susdb | Suspend-DbaAgDbDataMovement -Confirm:$false
# then, resume
$susdb | Resume-DbaAgDbDataMovement
# or 
Resume-DbaAgDbDataMovement -SqlInstance sql02 -AvailabilityGroup test-ag -Database pubs

Chapter 18

These tasks are performed in SQL Server Management Server.

Chapter 19

Find jobs that have been disabled

Find-DbaAgentJob -SqlInstance localhost -IsDisabled
# or for your whole estate!
Get-DbaRegServer | Find-DbaAgentJob -IsFailed
# or for your whole estate using another command if you'd like
Get-DbaRegisteredServer | Get-DbaAgentJob | Where Enabled -eq $false

Find jobs that have failed

Find-DbaAgentJob -SqlInstance localhost -IsFailed
# or for your whole estate!
Get-DbaRegServer | Find-DbaAgentJob -IsFailed

Chapter 20

Create a schedule that runs jobs every day at 6 in the morning

New-DbaAgentSchedule -SqlInstance sql01 -Schedule DailyAt6 -FrequencyType Daily -StartTime "060000"
# or associate it with a job
New-DbaAgentSchedule -SqlInstance sql01 -Job MyJob -Schedule DailyAt6 -FrequencyType Daily -StartTime "060000" -FrequencyInterval 1
# if using a dbatools version prior to 1.1.34, which requires FrequencyInterval
New-DbaAgentSchedule -SqlInstance sql01 -Job MyJob -Schedule DailyAt6 -FrequencyType Daily -StartTime "060000" -FrequencyInterval 1

View the job history on multiple SQL instances

Get-DbaAgentJobHistory -SqlInstance sql01, sql02, sql03
# or
Get-DbaRegisteredServer | Get-DbaAgentJobHistory

Find all jobs that are currently running within your estate

Get-DbaRegServer | Get-DbaRunningJob

Use Get-Help Start-DbaAgent with the -Parameter parameter to get more information about Wait and WaitPeriod

Get-Help Start-DbaAgent -Parameter Wait
Get-Help Start-DbaAgent -Parameter WaitPeriod

Chapter 21

Check if a specific database table has any column which contains potential PII data

Invoke-DbaDbPiiScan -SqlInstance workstationx -Database test -Verbose

Generate a data masking configuration file for a selection of tables and columns

New-DbaDbMaskingConfig -SqlInstance sql01 -Database pubs -Table Address -Column City, PostalCode -Path "d:\temp"

Anonymize the data using the data masking configuration file generated

Invoke-DbaDbDataMasking -SqlInstance sql01dev -Database pubs -FilePath "D:\temp\sql01.pubs.DataMaskingConfig.json"

Chapter 22

Explore New-DbaDacOption with different -Type and -Action parameters using Get-Member

New-DbaDacOption -Type Bacpac -Action Export | Get-Member
New-DbaDacOption -Type Dacpac -Action Export | Get-Member
New-DbaDacOption -Type Bacpac -Action Publish | Get-Member
New-DbaDacOption -Type Dacpac -Action Publish | Get-Member

Use your experience with looping or pipes to export more than one profile

$dbs = Get-DbaDatabase -SqlInstance sql01 -UserDbOnly

foreach ($db in $dbs) {
    New-DbaDacProfile -SqlInstance $db.Parent -Database $db.Name -Path C:\temp
}

Chapter 23

Find sessions in a stopped status

Get-DbaXESession -SqlInstance localhost | Where Status -eq "Stopped"

Create a session from a template and run it just for 5 minutes

# look for template commands
Get-Command -Module dbatools *template*
# get examples from Import-DbaXESessionTemplate
Get-Help Import-DbaXESessionTemplate -Examples
# The last example looks great! Select the session with the name Connection Detail Tracking
Get-DbaXESessionTemplate | Out-GridView -PassThru | Import-DbaXESessionTemplate -SqlInstance sql01
# Start it and run for 5 minutes
Start-DbaXESession -SqlInstance sql01 -Session "Connection Detail Tracking" -StartAt (Get-Date).AddMinutes(5)

Find the file location of the system_health session

Get-DbaXESessionTarget -SqlInstance localhost -Session system_health | Select File

Chapter 24

Hide your SQL Server instance

# look for hide commands
Get-Command -Module dbatools *hide*
# sweet, found it. let's hide two
Enable-DbaHideInstance -SqlInstance dbatoolslab\SQL2017, dbatoolslab

Verify if you have any database using TDE

Get-DbaDatabase -SqlInstance sql01 -Encrypted

Check if one of your backups was done using a certificate

Read-DbaBackupHeader -SqlInstance sql01 -Path C:\temp\mydb.bak | Select-Object KeyAlgorithm, EncryptorType, EncryptorThumbprint | Out-GridView

Chapter 25

Collect and store the current compression levels of your database using Get-DbaDbCompression

Get-DbaDbCompression -SqlInstance localhost | Write-DbaDataTable -SqlInstance localhost -Database compression -Table databaseinfo -AutoCreateTable

Review the compression suggestions from Test-DbaDbCompression, remember if your lab hasn't had much activity the workload information will be limited

Test-DbaDbCompression -SqlInstance sql01
# or
Test-DbaDbCompression -SqlInstance sql01 -Database mydb
# or
Test-DbaDbCompression -SqlInstance sql01, sql02
# or
Get-DbaRegServer | Test-DbaDbCompression | Out-GridView

Compress a single table with page compression using the -Table parameter of Set-DbaDbCompression

Set-DbaDbCompression -SqlInstance sql01 -Database mydb -CompressionType Page -Table table1

Apply the recommended compression suggestion to your entire database with Set-DbaDbCompression

Test-DbaDbCompression -SqlInstance localhost -Database mydb -OutVariable testCompression
Set-DbaDbCompression -SqlInstance localhost -Database mydb -InputObject $testCompression

Rerun Get-DbaDbCompression and compare your results to step 1, review your space savings and compression changes

# Run this then keep the GridView open
Invoke-DbaQuery -SqlInstance localhost -Database compression -Query "select * from databaseinfo" | Out-GridView
# Now run
Get-DbaDbCompression -SqlInstance localhost | Out-GridView

Chapter 26

Check the documentation. We have lots of blog posts from our contributors and users showing how to use it.

Install dbachecks

# run as admin, install for all users
Install-module dbachecks
# or, install it just for yourself
Install-Module dbachecks -Scope CurrentUser

Run a check to validate if your MaxMemory setting is configured correctly

Invoke-DbcCheck -Check MaxMemory -SqlInstance localhost

Explore all configurations and existing checks

# Explore available checks
Get-DbcCheck | Out-GridView
# Explore available configurations
Get-DbcConfig | Out-GridView

Chapter 27

Try a number of commands against Azure SQL Database and let us know which ones work for you at dbatools.io/issues

We'd like your help on this one! Connect to Azure using $server = Connect-DbaInstance .... then look for commands that you would find useful and run them. Log an issue at dbatools.io/issues and we'll know what to prioritize when it comes to making commands compatible (if it's possible).

Chapter 28

Confirm what is the actual value for the "batch separator" configuration

# filter down for the word batch
Get-DbatoolsConfig | Out-GridView -Passthru
# or
Get-DbatoolsConfig *batch*
# or 
Get-DbatoolsConfig -FullName formatting.batchseparator

Export/Import dbatools configurations related with formatting module

Get-DbatoolsConfig -Module formatting

Get the 5 five most recent error messages logged by dbatools

 Get-DbatoolsError -Last 5