dbatools

DBATools is a collection PowerShell command over 700 command that can help DBA to achieve common task in a more automated manner

in this section will demonstrate how to install dbatools , and how to use it deferent type of task that include

1- taking backup
2- taking script of the job
3-migiration
and so on

install DBATOOLS

first we need to install nuget package provider which handle the installing of dbatools and varies deferent package

lunch PowerShell as administrator and start by verifying of nuget installed or not by listing all packages provider using the below command

Get-PackageProvider -ListAvailable

nugget is not installed we will start by installing it using the below command

 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

 Install-PackageProvider -Name NuGet

now we can start installing DBATools by simply passing the below command

Install-Module dbatools

DBAtools is successfully installed

setup the variables

before you start using dbatools you need to set the variables which include details of the source sql server and destination sql server

the variables are setup as the below example

$new = "10.10.10.60"
$old = $instance = "localhost"
$allservers = $old, $new

you can also specify backpatch variable , and also shared path for migration we will demonstrate this in coming sections

you specify the Ip of the server or hostname of the server
pass the variables on PowerShell

resolving certification issue

sometimes when you run any of dbatools command you may face error related to certification to resolve it we will setup variables telling dbatools to trust the certificate

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Registe

taking script of local sql server

dbatools can script out the jops for local instance and store in file

Get-DbaAgentJob -SqlInstance $old | Export-DbaScript -Path C:\temp\jobs.sql

inside the folder you will find export script for each jop you have in the local instance

backing up logins

Export-DbaLogin can be use to take backup of the logins and store them in you local machine

Export-DbaLogin -SqlInstance $old -Path C:\temp\logins.sql
Invoke-Item C:\temp\logins.sql

migrating config

best part of dbatools that it can do full migration that include taking same instance setting from source and apply it on destination that include memory setting and so on.
move database mail , login , and as well as schema from source to destination
note
for this you will need to have shared path and full instance assess on both source and destination

setup the variables for migration as follow

$startDbaMigrationSplat = @{
    Source = $old
    Destination = $new
    BackupRestore = $true
    SharedPath = '\\SQL-STG-DB01\temp'
    
}

now run the below command that will start the migration and also display progress windows with stat of each task

Start-DbaMigration @startDbaMigrationSplat -Force | Select-Object * | Out-GridView

testing backup

dbatools provides a valuable feature to test your recent database backups by restoring a copy of the database, running DBCC CHECKDB to identify any issues, and then dropping the test database once the process is complete. This functionality is crucial for preparing for potential disasters by ensuring your backups are reliable and your databases are free from corruption.

Test-DbaLastBackup -SqlInstance $old  -Database [database-name]| Out-GridView

Test-DbaLastBackup -SqlInstance $old  -Database tools | Out-GridView

taking database snapshot

sql server provide you an ability to take database snapshot of your database , which will come very useful incase you planning to impalement a change and you want to revert the database to original stat as quickly as possible

unfounatly setup database snapshot can be a bit tricky
we can use dba tools to take database snapshot in a matter of 3 command

New-DbaDbSnapshot -SqlInstance $old  -Database dba -Name dba_snapshot

below command will list all database snapshot on the instance

Get-DbaDbSnapshot -SqlInstance $old

restore database using snapshot is made easy with dbatools using just one line command

install Ola

want to install ola script and setup job on local and remote instance
dbatools can make it faster for you

$old | Install-DbaMaintenanceSolution -InstallJobs [local instance]
$new | Install-DbaMaintenanceSolution -InstallJobs [remoet instance ]

get status of all your environment

sometime as dba we will task to take inventory of all database with there version
but when you have lots of db server running on your environment this task can become headache and can take to mush time

dbatools can help you finish this task in mater of second

$allservers | Get-DbaBuild

Check out how complete our sp_configure command is

another common task , often requested by cybersecurity team is to get list of all sp_configure

this task can also be a big headache.

again dbatools can help you get this report in matter of seconds

Get-DbaSpConfigure -SqlInstance $new | Out-GridView

Diagnostic query

dbatools can help get full report of instance in cvs format that include

• core count
• AG status
• missing index
• index fragmentation
• log space usage
• buffer usage
• table sizes
• statistics update status
• index usage

and many more which can be very useful we we as DBA are doing daily health check

$old | Invoke-DbaDiagnosticQuery -UseSelectionHelper | Export-DbaDiagnosticQuery -Path $home
Invoke-Item $home

masking

finally we will discussed data masking which
dbatools can help mask table very fast on multiple server

below is example of command to mask column in table

New-DbaDbMaskingConfig -SqlInstance $old -Database AdventureWorks2019  -Table Address -Column PostalCode -Path C:\Temp\clone

the command will also export a file that have masking config for the table

there lots of command that dbatools provide you can explore them by visiting the below website

https://dbatools.io/commands