Skip to content

Instantly share code, notes, and snippets.

Last active December 14, 2022 00:06
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save potatoqualitee/e8932b64aeb6ef404e252d656b6318a2 to your computer and use it in GitHub Desktop.
Save potatoqualitee/e8932b64aeb6ef404e252d656b6318a2 to your computer and use it in GitHub Desktop.
# Set some vars
$new = "localhost\sql2016"
$old = $instance = "localhost"
$allservers = $old, $new
# Alternatively, use Registered Servers?
Get-DbaCmsRegServer -SqlInstance $instance | Out-GridView
# Quick overview of commands
# Need to restore a database? It can be as simple as this:
Restore-DbaDatabase -SqlInstance $instance -Path "C:\temp\AdventureWorks2012-Full Database Backup.bak"
# But what if the database already exists? You'll be warned to add -WithReplace
Restore-DbaDatabase -SqlInstance $instance -Path "C:\temp\AdventureWorks2012-Full Database Backup.bak" -WithReplace
# Use Ola Hallengren's backup script? We can restore an *ENTIRE INSTNACE* with just one line
Get-ChildItem -Directory \\workstation\backups\sql2012 | Restore-DbaDatabase -SqlInstance $new
# What about if you need to make a backup? And you are logging in with alternative credentials?
Get-DbaDatabase -SqlInstance $new -SqlCredential (Get-Credential sa) | Backup-DbaDatabase
# Testing your backups is crazy easy!
Test-DbaLastBackup -SqlInstance $old | Out-GridView
# But what if you want to test your backups on a different server?
Test-DbaLastBackup -SqlInstance $old -Destination $new | Out-GridView
# Nowadays, we don't just backup databases. Now, we're backing up logins
Export-DbaLogin -SqlInstance $instance -Path C:\temp\logins.sql
Invoke-Item C:\temp\logins.sql
# And Agent Jobs
Get-DbaAgentJob -SqlInstance $old | Export-DbaScript -Path C:\temp\jobs.sql
# What if you just want to script out your restore?
Get-ChildItem -Directory \\workstation\backups\subset\ | Restore-DbaDatabase -SqlInstance $new -OutputScriptOnly -WithReplace | Out-File -Filepath c:\temp\restore.sql
Invoke-Item c:\temp\restore.sql
# You've probably heard about how easy migrations can be with dbatools. Here's an example
$startDbaMigrationSplat = @{
Source = $old
Destination = $new
BackupRestore = $true
SharedPath = 'C:\temp'
Exclude = 'BackupDevices','SysDbUserObjects','Credentials'
Start-DbaMigration @startDbaMigrationSplat -Force | Select * | Out-GridView
# Know how snapshots used to be a PITA? Now they're super easy
New-DbaDbSnapshot -SqlInstance $new -Database db1 -Name db1_snapshot
Get-DbaDbSnapshot -SqlInstance $new
Get-DbaProcess -SqlInstance $new -Database db1 | Stop-DbaProcess
Restore-DbaFromDatabaseSnapshot -SqlInstance $new -Database db1 -Snapshot db1_snapshot
Remove-DbaDbSnapshot -SqlInstance $new -Snapshot db1_snapshot # or -Database db1
# Have you tested your last good DBCC CHECKDB? We've got a command for that
$old | Get-DbaLastGoodCheckDb | Out-GridView
# Here's how you can find your integrity jobs and easily start them. Then, you can watch them run, and finally check your newest DBCC CHECKDB results
$old | Get-DbaAgentJob | Where Name -match integrity | Start-DbaAgentJob
$old | Get-DbaRunningJob
$old | Get-DbaLastGoodCheckDb | Out-GridView
# Our new build website is super useful!
# You can use the same JSON the website uses to check the status of your own environment
$allservers | Get-DbaBuildReference
# We evaluated 37,545 SQL Server stored procedures on 9 servers in 8.67 seconds!
$new | Find-DbaStoredProcedure -Pattern dbatools
# Check out the differences when you use Select *
$new | Find-DbaStoredProcedure -Pattern dbatools | Select * | Out-GridView
# Here's how you can search for email patterns
$new | Find-DbaStoredProcedure -Pattern '\w+@\w+\.\w+'
# Have an employee who is leaving? Find all of their objects.
$allservers | Find-DbaUserObject -Pattern ad\jdoe | Out-GridView
# Find detached databases, by example
Detach-DbaDatabase -SqlInstance $instance -Database AdventureWorks2012
Find-DbaOrphanedFile -SqlInstance $instance | Out-GridView
# Find it! - JSON file powers command and website search
Find-DbaCommand Backup
Find-DbaCommand -Tag Backup | Out-GridView
# View and change service account
Get-DbaService -ComputerName workstation | Out-GridView
Get-DbaService -ComputerName workstation | Select * | Out-GridView
Get-DbaService -Instance SQL2016 -Type Agent | Update-DbaServiceAccount -Username 'Local system'
# Check out how complete our sp_configure command is
Get-DbaSpConfigure -SqlInstance $new | Out-GridView
Get-DbaSpConfigure -SqlInstance $new -ConfigName XPCmdShellEnabled
# Easily update configuration values
Set-DbaSpConfigure -SqlInstance $new -ConfigName XPCmdShellEnabled -Value $true
# DB Cloning too!
Invoke-DbaDbClone -SqlInstance $new -Database db1 -CloneDatabase db1_clone | Out-GridView
# XEvents - more coming soon, like easy replays on remote servers
Get-DbaXESession -SqlInstance $new
$session = Get-DbaXESession -SqlInstance $new -Session system_health | Stop-DbaXESession
$session | Start-DbaXESession
# Read and watch
Get-DbaXESession -SqlInstance $new -Session system_health | Read-DbaXEFile
Get-DbaXESession -SqlInstance $new -Session system_health | Read-DbaXEFile | Select -ExpandProperty Fields | Out-GridView
# Reset-DbaAdmin
Reset-DbaAdmin -SqlInstance $instance -Login sqladmin -Verbose
Get-DbaDatabase -SqlInstance $instance -SqlCredential (Get-Credential sqladmin)
# sp_whoisactive
Install-DbaWhoIsActive -SqlInstance $instance -Database master
Invoke-DbaWhoIsActive -SqlInstance $instance -ShowOwnSpid -ShowSystemSpids
# Diagnostic query!
$instance | Invoke-DbaDiagnosticQuery -UseSelectionHelper | Export-DbaDiagnosticQuery -Path $home
Invoke-Item $home
# Ola, yall
$instance | Install-DbaMaintenanceSolution -ReplaceExisting -BackupLocation C:\temp -InstallJobs
# Startup parameters
Get-DbaStartupParameter -SqlInstance $instance
Set-DbaStartupParameter -SqlInstance $instance -SingleUser -WhatIf
# Database clone
Invoke-DbaDbClone -SqlInstance $new -Database dbwithsprocs -CloneDatabase dbwithsprocs_clone
# Schema change and Pester tests
Invoke-DbaQuery -SqlInstance $new -Database tempdb -Query "CREATE TABLE dbatoolsci_schemachange (id int identity)"
Invoke-DbaQuery -SqlInstance $new -Database tempdb -Query "EXEC sp_rename 'dbatoolsci_schemachange', 'dbatoolsci_schemachange_new'"
Get-DbaSchemaChangeHistory -SqlInstance $new -Database tempdb
Invoke-DbaQuery -SqlInstance $new -Database tempdb -Query "DROP TABLE dbatoolsci_schemachange_new"
# Get Db Free Space AND write it to table
Get-DbaDbSpace -SqlInstance $instance | Out-GridView
Get-DbaDbSpace -SqlInstance $instance -IncludeSystemDB | ConvertTo-DbaDataTable | Write-DbaDataTable -SqlInstance $instance -Database tempdb -Table DiskSpaceExample -AutoCreateTable
Invoke-DbaQuery -ServerInstance $instance -Database tempdb -Query 'SELECT * FROM dbo.DiskSpaceExample' | Out-GridView
# History
Get-Command -Module dbatools *history*
# More histories
Get-DbaAgentJobHistory -SqlInstance $instance | Out-GridView
Get-DbaBackupHistory -SqlInstance $new | Out-GridView
# Identity usage
Test-DbaIdentityUsage -SqlInstance $instance | Out-GridView
# Test/Set SQL max memory
$allservers | Get-DbaMaxMemory
$allservers | Test-DbaMaxMemory | Format-Table
$allservers | Test-DbaMaxMemory | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-DbaMaxMemory -WhatIf
Set-DbaMaxMemory -SqlInstance $instance -MaxMb 1023
# Test recovery models for "pseudo simple"
Test-DbaDbRecoveryModel -SqlInstance $new
Test-DbaDbRecoveryModel -SqlInstance $new | Where { $_.ConfiguredRecoveryModel -ne $_.ActualRecoveryModel }
# Testing sql server linked server connections
Test-DbaLinkedServerConnection -SqlInstance $instance
# See protocols
Get-DbaServerProtocol -ComputerName $instance | Out-GridView
# SQL Modules - View, TableValuedFunction, DefaultConstraint, StoredProcedure, Rule, InlineTableValuedFunction, Trigger, ScalarFunction
Get-DbaModule -SqlInstance $instance | Out-GridView
Get-DbaModule -SqlInstance $instance -ModifiedSince (Get-Date).AddDays(-7) | Select-String -Pattern sp_executesql
# Reads trace files - default trace by default
Read-DbaTraceFile -SqlInstance $instance | Out-GridView
# Get the registry root
Get-DbaRegistryRoot -ComputerName $instance
# don't have remoting access? Explore the filesystem. Uses master.sys.xp_dirtree
Get-DbaFile -SqlInstance $instance
Get-DbaFile -SqlInstance $instance -Depth 3 -Path 'C:\Program Files\Microsoft SQL Server' | Out-GridView
New-DbaDirectory -SqlInstance $instance -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\test'
# Test your SPNs and see what'd happen if you'd set them
$servers | Test-DbaSpn | Out-GridView
$servers | Test-DbaSpn | Out-GridView -PassThru | Set-DbaSpn -WhatIf
# Get Virtual Log File information
Get-DbaDbVirtualLogFile -SqlInstance $new -Database db1
Get-DbaDbVirtualLogFile -SqlInstance $new -Database db1 | Measure-Object
# Out-GridView madness <3
Get-DbaDatabase -SqlInstance $old | Out-GridView -PassThru | Copy-DbaDatabase -Destination $new -BackupRestore -SharedPath \\workstation\c$\temp -Force
# We've even got our own config system!
Get-DbatoolsConfig | Out-GridView
# Check out our logs directory, so Enterprise :D
Invoke-Item (Get-DbatoolsConfig -FullName path.dbatoolslogpath).Value
# Want to see what's in our logs?
Get-DbatoolsLog | Out-GridView
# Need to send us diagnostic information? Use this support package generator
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment