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.
Get-Command *dbareg* -Module dbatools
# or
Get-Command *dbareg*
# or
Find-DbaCommand dbareg
Get-Help Install-DbaInstance -Examples
# or
Get-Help Install-DbaInstance -Detailed
Get-DbaService -ComputerName server01
Get-DbaService -ComputerName server01 -InstanceName SQLEXPRESS
Get-DbaService -ComputerName sql2016 -Type Agent |
Select-Object ComputerName, InstanceName, StartName
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) }
Get-DbaDatabase -SqlInstance sql2017 | Where { $PSItem.LastFullBackup -eq 0 }
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
Get-DbaLogin -SqlInstance sql2017 | Write-DbaDbTableData -SqlInstance sql2016 -AutoCreateTable -Database tempdb -Table logins
Invoke-DbaQuery -SqlInstance sql2016 -Database tempdb -Query "select * from logins"
Get-Service -ComputerName sql2017 | Select * | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table services -AutoCreateTable
Get-ChildItem -File | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table files -AutoCreateTable
Get-DbaService -ComputerName sql2017 | Select * | Write-DbaDbTableData -SqlInstance sql2016 -Database tempdb -Table sqlservices -AutoCreateTable
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
$dbs = Get-DbaDatabase -SqlInstance $servers | Select ComputerName, SqlInstance, Name, SizeMB, LastRead, LastWrite
Write-DbaDbTableData -InputObject $dbs -SqlInstance sql2016 -Database tempdb -Table alldbs -AutoCreateTable
Get-ADComputer -Filter "Name -like '*SQL*'" | Find-DbaInstance
Find-DbaInstance -ComputerName sql2016 | Select -ExpandProperty Services | Where-Object DisplayName -match Report
Find-DbaInstance -ComputerName sql2016 -ScanType TCPPort -TCPPort 49837
Get-DbaRegServer | Test-DbaBuild -MinimumBuild 12.0.4511
# or
Test-DbaBuild -SqlInstance sql2017, sql2016 -Latest
Get-DbaRegServer | Get-DbaDatabase | Where Owner -eq sa
# or
Find-DbaUserObject -SqlInstance sql2017 -Pattern sa
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
Get-DbaRegServer -SqlInstance centralsql | Get-DbaRunningJob
# 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
New-DbaLogin -SqlInstance localhost -Login newlogin
# First create a database
New-DbaDatabase -SqlInstance localhost -Name test
# Then create the user
New-DbaDbUser -SqlInstance localhost -Database test -Login newlogin
Export-DbaUser -SqlInstance localhost -FilePath C:\temp\dbusers.sql
Get-DbaUserPermission -SqlInstance localhost | Export-Excel C:\temp\userpermissions.xlsx
Backup-DbaDatabase -SqlInstance sql01 -Path \\nas\sql\backups
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
# Refresh your brain about the syntax
Get-Help Find-DbaBackup -Examples
# Run the command
Find-DbaBackup -Path \\nas\sql\backups -BackupFileExtension trn -RetentionPeriod 21d
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-DbaDatabase -SqlInstance sql01 -Path \\nas\sql\demo\full.bak -DestinationDataDirectory D:\Data -DestinationLogDirectory L:\Log
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
Get-DbaDatabase -SqlInstance localhost -UserDbOnly | New-DbaDbSnapshot -NameSuffix snapz
# or
New-DbaDbSnapshot -SqlInstance localhost -Database test, new -NameSuffix snapz
New-DbaDbSnapshot -SqlInstance localhost -Database test, new -Path S:\dbsnapshots
# 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"
Remove-DbaDbSnapshot -SqlInstance localhost -Database test
# or
Get-DbaDbSnapshot -SqlInstance localhost | Remove-DbaDbSnapshot -Confirm:$false
Install-DbaInstance -SqlInstance localhost -Path C:\temp -Version 2017 -Feature Default -WhatIf
Update-DbaInstance -ComputerName sql2017\sqlexpress, server01 -Version CU3 -Download -Path \\network\share -WhatIf
Get-Help Update-DbaInstance -Examples
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
# look through export commands
Get-Command -Module dbatools *export*
# see the one you want
Get-DbaXESession -SqlInstance localhost | Export-DbaXESession -Path C:\temp
Get-DbaAgentJob -SqlInstance localhost | Export-DbaScript -Path C:\temp
Start-DbaMigration -Source dbatoolslab\SQL2017 -Destination dbatoolslab -BackupRestore -SharedPath "\\fileserver\share\sqlbackups\Migration"
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
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.
# 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
Get-DbaAgReplica -SqlInstance sql01, sql02
Get-DbaAgDatabase -SqlInstance sql01, sql02
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
These tasks are performed in SQL Server Management Server.
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-DbaAgentJob -SqlInstance localhost -IsFailed
# or for your whole estate!
Get-DbaRegServer | Find-DbaAgentJob -IsFailed
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
Get-DbaAgentJobHistory -SqlInstance sql01, sql02, sql03
# or
Get-DbaRegisteredServer | Get-DbaAgentJobHistory
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
Invoke-DbaDbPiiScan -SqlInstance workstationx -Database test -Verbose
New-DbaDbMaskingConfig -SqlInstance sql01 -Database pubs -Table Address -Column City, PostalCode -Path "d:\temp"
Invoke-DbaDbDataMasking -SqlInstance sql01dev -Database pubs -FilePath "D:\temp\sql01.pubs.DataMaskingConfig.json"
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
$dbs = Get-DbaDatabase -SqlInstance sql01 -UserDbOnly
foreach ($db in $dbs) {
New-DbaDacProfile -SqlInstance $db.Parent -Database $db.Name -Path C:\temp
}
Get-DbaXESession -SqlInstance localhost | Where Status -eq "Stopped"
# 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)
Get-DbaXESessionTarget -SqlInstance localhost -Session system_health | Select File
# look for hide commands
Get-Command -Module dbatools *hide*
# sweet, found it. let's hide two
Enable-DbaHideInstance -SqlInstance dbatoolslab\SQL2017, dbatoolslab
Get-DbaDatabase -SqlInstance sql01 -Encrypted
Read-DbaBackupHeader -SqlInstance sql01 -Path C:\temp\mydb.bak | Select-Object KeyAlgorithm, EncryptorType, EncryptorThumbprint | Out-GridView
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
Set-DbaDbCompression -SqlInstance sql01 -Database mydb -CompressionType Page -Table table1
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
Check the documentation. We have lots of blog posts from our contributors and users showing how to use it.
# run as admin, install for all users
Install-module dbachecks
# or, install it just for yourself
Install-Module dbachecks -Scope CurrentUser
Invoke-DbcCheck -Check MaxMemory -SqlInstance localhost
# Explore available checks
Get-DbcCheck | Out-GridView
# Explore available configurations
Get-DbcConfig | Out-GridView
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).
# filter down for the word batch
Get-DbatoolsConfig | Out-GridView -Passthru
# or
Get-DbatoolsConfig *batch*
# or
Get-DbatoolsConfig -FullName formatting.batchseparator
Get-DbatoolsConfig -Module formatting
Get-DbatoolsError -Last 5