getting-started
# 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 | |
Start-Process https://dbatools.io/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! | |
Start-Process https://dbatools.io/Test-DbaLastBackup | |
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! | |
Start-Process https://dbatools.io/builds | |
# 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 | |
New-DbatoolsSupportPackage |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment