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