Skip to content

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