DBATools...... |
|
If you haven't used DBATools module with powershell, you need too. I will document some of the problems I have solved using this great module!!
dbatools.io/commands/
dbatools.io/commands/
Backup and Restore
Backup-DbaDatabase -sqlinstance <server name> -Path 'path to backups' -type Full -FileCount 8 -ExcludeDatabase master -CreateFolder
Restore-DBADatabase -sqlinstance <server name> -Path 'path to backups' -NoRecovery -WithReplace -directoryRecurse
Restore-DBADatabase -sqlinstance <server name> -Path 'path to backups' -NoRecovery -WithReplace -directoryRecurse
Scripted SQL Install with DBATools |
|
Here is script to install SQL 2019. Its a rough outline of how to do it. Lots of options and I have found that DBATools can handle pretty much any scenario.
param($sqlinstance = "<ServerName>",
$tempdbdata = "T:\TempDB\Data",
$tempdblog = "T:\TempDB\Log",
$BackupDir = "D:\SQL_Backups",
$DataPath = "D:\SQL_Data",
$LogPath = "L:\SQL_Log",
$InstancePath = "D:\SQL",
$LocalInstall = "D:\2019_Installer",
$DBEngineAcct = "NT AUTHORITY\SYSTEM", $AgentAcct = "NT AUTHORITY\SYSTEM",
$MaxDop = 4,
$Version = 2019,
$updateSQLAccount = "<account to replace NT Authority>" )
import-module DBATools
##tempdb config could be done by DBATools
$config = @{
SQLTEMPDBDIR = $tempdbdata
SQLTEMPDBLOGDIR = $tempdblog
SQLBACKUPDIR = $BackupDir
FILESTREAMLEVEL = "0"
TCPENABLED = "1"
NPENABLED = "0"
SQLSVCACCOUNT = "$DBEngineAcct"
AGTSVCACCOUNT = "$AgentAcct"
SQLCOLLATION = "SQL_Latin1_General_CP1_CI_AS"
SQLSVCSTARTUPTYPE = "Automatic"
SQLSYSADMINACCOUNTS = "<SA accounts>"
SQLTEMPDBFILECOUNT = "8"
SQLTEMPDBFILESIZE = "1024"
SQLTEMPDBFILEGROWTH = "256"
SQLTEMPDBLOGFILESIZE = "512"
SQLTEMPDBLOGFILEGROWTH = "256"
SQLMAXDOP = "4"
}
Write-Information "Installing SQL Server"
Install-DbaInstance -SqlInstance $sqlinstance `
-Version $Version `
-Feature Engine`
-DataPath $DataPath `
-LogPath $LogPath `
-InstancePath $InstancePath `
-AuthenticationMode Windows `
-AdminAccount "<DBA accounts>" `
-port $port `
-SaveConfiguration C:\InstallScripts `
-Configuration $config `
-Confirm:$false `
-Path $LocalInstall `
-updatesourcepath $localinstall `
-verbose
Write-Information "Post Install Configuration"
start-sleep -Seconds 5
Write-Information "Enabling Trace Flag 3226 as Startup Param to disable backup logging"
Set-DbaStartupParameter -SqlInstance "$($sqlinstance)" -TraceFlag 3226 -Force
Write-Information "Set PowerPlan to High Performance"
Set-DbaPowerPlan -ComputerName $($ComputerName) -PowerPlan 'High Performance'
Write-Information "Set Max Server Memory"
Set-DbaMaxMemory -SqlInstance "$($sqlinstance)"
Write-Information "Set Max DOP,CTP, XP_CMDShell and DAC"
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name XPCmdShellEnabled -Value 1
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)"-Name 'remote admin connections' -Value 1
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name 'cost threshold for parallelism' -Value 75
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name 'max degree of parallelism' -Value 4
Set-DbaSPConfigure -SqlInstance "$($sqlinstance)" -Name 'Agent XPs' -Value 1
Set-DbaSPConfigure -SqlInstance "$($sqlinstance)" -Name 'Database Mail XPs' -Value 1
Write-Information "Enabling HADR"
Enable-DbaAgHadr -SqlInstance "$($sqlinstance)" -Force
Write-Information "setting GMSA for SQL Server"
Get-DbaService -Type Engine,Agent | Update-DbaServiceAccount -Username $updateSQLAccount -EnableException
Write-Information "Setting Instant File Initialization, Lock Pages in Memory, Logon as Batch and Generate Security Audits"
Set-DbaPrivilege -ComputerName $($ComputerName) -Type IFI, LPIM, BatchLogon, SecAudit
Write-Information "Restart SQL"
Get-DbaService -ComputerName $($ComputerName) -Type Agent,Engine | Restart-DbaService -Force
param($sqlinstance = "<ServerName>",
$tempdbdata = "T:\TempDB\Data",
$tempdblog = "T:\TempDB\Log",
$BackupDir = "D:\SQL_Backups",
$DataPath = "D:\SQL_Data",
$LogPath = "L:\SQL_Log",
$InstancePath = "D:\SQL",
$LocalInstall = "D:\2019_Installer",
$DBEngineAcct = "NT AUTHORITY\SYSTEM", $AgentAcct = "NT AUTHORITY\SYSTEM",
$MaxDop = 4,
$Version = 2019,
$updateSQLAccount = "<account to replace NT Authority>" )
import-module DBATools
##tempdb config could be done by DBATools
$config = @{
SQLTEMPDBDIR = $tempdbdata
SQLTEMPDBLOGDIR = $tempdblog
SQLBACKUPDIR = $BackupDir
FILESTREAMLEVEL = "0"
TCPENABLED = "1"
NPENABLED = "0"
SQLSVCACCOUNT = "$DBEngineAcct"
AGTSVCACCOUNT = "$AgentAcct"
SQLCOLLATION = "SQL_Latin1_General_CP1_CI_AS"
SQLSVCSTARTUPTYPE = "Automatic"
SQLSYSADMINACCOUNTS = "<SA accounts>"
SQLTEMPDBFILECOUNT = "8"
SQLTEMPDBFILESIZE = "1024"
SQLTEMPDBFILEGROWTH = "256"
SQLTEMPDBLOGFILESIZE = "512"
SQLTEMPDBLOGFILEGROWTH = "256"
SQLMAXDOP = "4"
}
Write-Information "Installing SQL Server"
Install-DbaInstance -SqlInstance $sqlinstance `
-Version $Version `
-Feature Engine`
-DataPath $DataPath `
-LogPath $LogPath `
-InstancePath $InstancePath `
-AuthenticationMode Windows `
-AdminAccount "<DBA accounts>" `
-port $port `
-SaveConfiguration C:\InstallScripts `
-Configuration $config `
-Confirm:$false `
-Path $LocalInstall `
-updatesourcepath $localinstall `
-verbose
Write-Information "Post Install Configuration"
start-sleep -Seconds 5
Write-Information "Enabling Trace Flag 3226 as Startup Param to disable backup logging"
Set-DbaStartupParameter -SqlInstance "$($sqlinstance)" -TraceFlag 3226 -Force
Write-Information "Set PowerPlan to High Performance"
Set-DbaPowerPlan -ComputerName $($ComputerName) -PowerPlan 'High Performance'
Write-Information "Set Max Server Memory"
Set-DbaMaxMemory -SqlInstance "$($sqlinstance)"
Write-Information "Set Max DOP,CTP, XP_CMDShell and DAC"
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name XPCmdShellEnabled -Value 1
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)"-Name 'remote admin connections' -Value 1
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name 'cost threshold for parallelism' -Value 75
Set-DbaSpConfigure -SqlInstance "$($sqlinstance)" -Name 'max degree of parallelism' -Value 4
Set-DbaSPConfigure -SqlInstance "$($sqlinstance)" -Name 'Agent XPs' -Value 1
Set-DbaSPConfigure -SqlInstance "$($sqlinstance)" -Name 'Database Mail XPs' -Value 1
Write-Information "Enabling HADR"
Enable-DbaAgHadr -SqlInstance "$($sqlinstance)" -Force
Write-Information "setting GMSA for SQL Server"
Get-DbaService -Type Engine,Agent | Update-DbaServiceAccount -Username $updateSQLAccount -EnableException
Write-Information "Setting Instant File Initialization, Lock Pages in Memory, Logon as Batch and Generate Security Audits"
Set-DbaPrivilege -ComputerName $($ComputerName) -Type IFI, LPIM, BatchLogon, SecAudit
Write-Information "Restart SQL"
Get-DbaService -ComputerName $($ComputerName) -Type Agent,Engine | Restart-DbaService -Force
Test your Backups with DBATools |
|
|
|