Useful Instance Migration/Maintenance Commands¶
Set Trace Flags¶
1 | Set-DbaStartupParameter -Traceflags 3226,4199,7412,460 |
| TRACE FLAG | PURPOSE |
|---|---|
| 460 | Enable detailed String or Binary Data would be truncated error message in a future Cumulative Update |
| 3226 | Suppress successful backup messages in the error log |
| 4199 | Enable query optimizer fixes in CUs and hotfixes |
| 7412 | Enable lightweight execution statistics profiling |
Get all disabled logins and export them as SQL scripts¶
1 | Get-DbaLogin -ServerInstance $OldInstance | Where-Object {$_.isDisabled} | ForEach-Object {Export-DbaLogin -Login $_ -Path (Join-Path -path s:\Temp\2016Migration\Logins -Childpath "$($_.name.replace('\','$')).sql") -SqlInstance $OldInstance} |
Get all enabled logins¶
1 | $LoginsToCopy = Get-DbaLogin -sqlinstance $OldInstance|Where-Object {-not $_.IsDisabled} |
Copy enabled logins¶
1 | Copy-DbaLogin -Source $OldInstance -Destination $NewInstance -Login $LoginsToCopy.Name -Verbose |
Export current jobs to scripts¶
1 2 | Get-DbaAgentJob -SqlInstance $OldInstance | Where-Object {-not $_.isenabled} | ForEach-Object {Export-DbaScript $_ -Path (Join-Path -Path s:\Temp\2016Migration\DisabledJobs -Childpath "$($_.name.replace('\','$')).sql")} Get-DbaAgentJob -SqlInstance $OldInstance | Where-Object {$_.isenabled} | ForEach-Object {Export-DbaScript $_ -Path (Join-Path -Path s:\Temp\2016Migration\EnabledJobs -Childpath "$($_.name.replace('\','$')).sql")} |
Get only the enabled job¶
1 | $JobsToCopy = Get-DbaAgentJob -SqlInstance $OldInstance -ExcludeDisabledJobs |
Copy the Operator(s) from the existing server¶
1 | Copy-DbaAgentOperator -Source $oldinstance -Destination $newinstance |
Copy only the enabled jobs and disable on the new server¶
1 | Copy-DbaAgentJob -Source $OldInstance -Destination $NewInstance -Job $JobsToCopy.Name -verbose -DisableOnDestination |
Copy Logins¶
1 | Copy-DbaLogin -Source 'SQL01\Instance01' -Destination 'SQL02\Instance02' -Force |
Migrate data to new instance¶
1 2 3 4 5 6 7 8 9 10 | $params = @{ Source = 'SQL01\Instance01' Destination = 'SQL02\Instance02' SharedPath = '\\SQL02\Temp' BackupRestore = $true ReuseSourceFolderStructure = $false Force = $true } Start-DbaMigration @params -Verbose |
Set Buffer Pool Extension¶
1 2 3 | ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON ( FILENAME = 'D:\SQLTEMP\ExtensionFile.BPE' , SIZE = 64GB ) |
Move TempDB¶
1 2 3 4 5 6 7 8 9 10 | USE MASTER GO ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\SQLTEMP\tempdb.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev2, FILENAME= 'D:\SQLTEMP\tempdb2.ndf') GO ALTER DATABASE tempdb MODIFY FILE (name = tempdev3, filename = 'D:\SQLTEMP\tempdb3.ndf') GO ALTER DATABASE tempdb MODIFY FILE (name = tempdev4, filename = 'D:\SQLTEMP\tempdb4.ndf') GO |
Brent Ozar’s First Responder Kit¶
1 | Install-DbaFirstResponderKit-database master -force |
Adam Machanic’s sp_whoisactive¶
1 | Install-DbaWhoIsActive-database master |
Ola Hallengren’s Maintenance Solution¶
1 | Install-DbaMaintenanceSolution-database master |