GitHub - sqlcollaborative/dbatools: 🚀 SQL Server automation and instance migrations have never been safer, faster or freer
Tue Dec 01 2020 21:20:16 GMT+0000 (UTC)
Posted by @rtsdeveloper #powershell #sql #dbatools
# Set some vars $new = "localhost\sql2016" $old = $instance = "localhost" $allservers = $old, $new # Alternatively, use Registered Servers $allservers = Get-DbaCmsRegServer -SqlInstance $instance # Need to restore a database? It can be as simple as this: Restore-DbaDatabase -SqlInstance $instance -Path "C:\temp\AdventureWorks2012-Full Database Backup.bak" # Use Ola Hallengren's backup script? We can restore an *ENTIRE INSTANCE* 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 sqladmin | 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 = 'BackupDevice','SysDbUserObjects','Credentials' } Start-DbaMigration @startDbaMigrationSplat -Force | Select-Object * | 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-Object 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 # 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 # Check out how complete our sp_configure command is Get-DbaSpConfigure -SqlInstance $new | Out-GridView # 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 # Read and watch XEvents Get-DbaXESession -SqlInstance $new -Session system_health | Read-DbaXEFile Get-DbaXESession -SqlInstance $new -Session system_health | Read-DbaXEFile | Select-Object -ExpandProperty Fields | Out-GridView # Reset-DbaAdmin Reset-DbaAdmin -SqlInstance $instance -Login sqladmin -Verbose Get-DbaDatabase -SqlInstance $instance -SqlCredential 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 Get-DbaSchemaChangeHistory -SqlInstance $new -Database tempdb # 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 -SqlInstance $instance -Database tempdb -Query 'SELECT * FROM dbo.DiskSpaceExample' | Out-GridView # History Get-Command -Module dbatools *history* # 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 # Testing sql server linked server connections Test-DbaLinkedServerConnection -SqlInstance $instance # See protocols Get-DbaServerProtocol -ComputerName $instance | Out-GridView # Reads trace files - default trace by default Read-DbaTraceFile -SqlInstance $instance | Out-GridView # don't have remoting access? Explore the filesystem. Uses master.sys.xp_dirtree Get-DbaFile -SqlInstance $instance # 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
Sample Scripts of Demo Code
https://github.com/sqlcollaborative/dbatools/#getting-started
Comments