Add-dtcAgDatabase -PrimaryInstance "SQL01" -DatabaseName "MyApp" ` 
-AvailabilityGroup "AG_Prod"

Add-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "MyApp","AppLog","AppAudit" ` 
-AvailabilityGroup "AG_Prod"

Add-dtcAgDatabase -PrimaryInstance "SQL01" -All ` 
-AvailabilityGroup "AG_Prod"

Add-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "App_*" ` 
-AvailabilityGroup "AG_Prod"

Add-dtcAgDatabase -PrimaryInstance "SQL01" -DatabaseName "MyApp" ` 
-AvailabilityGroup "AG_Prod" -SkipAutoSeedCheck

Add-dtcAgDatabase -PrimaryInstance "SQL01" -DatabaseName "MyApp" ` 
-AvailabilityGroup "AG_Prod" -WhatIf

Enable-dtcAgAutoSeed -PrimaryInstance "SQL01" -AvailabilityGroup "AG_Prod"

Enable-dtcAgAutoSeed -PrimaryInstance "SQL01" -AvailabilityGroup "AG_Prod" -WhatIf

Get-DbaAvailabilityGroup -SqlInstance "SQL01" | 
ForEach-Object { 
Enable-dtcAgAutoSeed -PrimaryInstance "SQL01" ` 
-AvailabilityGroup $_.Name -Verbose 
}

$cred = Get-Credential 
Enable-dtcAgAutoSeed -PrimaryInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" -Credential $cred

Get-dtcAgHealthReport

Get-dtcAgHealthReport -SqlInstance "SQL01" ` 
-MaxRedoQueueMB 50 -MaxSendQueueMB 20

Get-dtcAgHealthReport -SqlInstance "SQL01","SQL02","SQL03" ` 
-ContinueOnError

Get-dtcAgHealthReport -SqlInstance "SQL01" | 
Where-Object OverallStatus -eq 'Critical' | 
Format-Table AgName, ReplicaName, Database, DbSyncState, RedoQueueMB -AutoSize

Get-dtcAgHealthReport -SqlInstance "SQL01" | 
Where-Object Role -eq 'SECONDARY' | 
Sort-Object RedoQueueMB -Descending | 
Select-Object AgName, Database, ReplicaName, RedoQueueMB, SendQueueMB | 
Format-Table -AutoSize

# Alle 30 Sekunden aktuellen Status anzeigen 
while ($true) { 
Clear-Host 
Get-dtcAgHealthReport -SqlInstance "SQL01" | 
Where-Object Role -eq 'SEEDING' | 
Select-Object Database, DbSyncState | 
Format-Table -AutoSize 
Start-Sleep 30 
}

Invoke-dtcAgFailoverCheck -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod"

Invoke-dtcAgFailoverCheck -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-TargetReplica "SQL02"

Invoke-dtcAgFailoverCheck -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-CheckSpn

$check = Invoke-dtcAgFailoverCheck -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" 
 
switch ($check.GoNoGo) { 
'GO' { Write-Host "Failover kann durchgeführt werden" -ForegroundColor Green } 
'GO-WITH-WARNING' { 
Write-Warning "Failover möglich aber mit Einschränkungen:" 
$check.CheckDetails | Where-Object Status -eq 'WARN' | Format-Table Check, Detail 
} 
'NO-GO' { 
Write-Error "Failover NICHT empfohlen!" 
$check.CheckDetails | Where-Object Status -eq 'FAIL' | Format-Table Check, Detail 
return 
} 
}

Get-DbaAvailabilityGroup -SqlInstance "SQL01" | 
ForEach-Object { 
Invoke-dtcAgFailoverCheck -SqlInstance "SQL01" ` 
-AvailabilityGroup $_.Name 
} | Select-Object AvailabilityGroup, GoNoGo, Passed, Warnings, Failed | 
Format-Table -AutoSize

New-dtcAgRepairJob -SqlInstance "SQL01"

New-dtcAgRepairJob -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod"

New-dtcAgRepairJob -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-ScheduleType Daily -ScheduleTime "03:30" ` 
-OperatorName "DBA-Team"

New-dtcAgRepairJob -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-ScheduleType EveryNMin -IntervalMinutes 30

"SQL01","SQL02","SQL03" | ForEach-Object { 
New-dtcAgRepairJob -SqlInstance $_ ` 
-AvailabilityGroup "AG_Prod" ` 
-ScheduleType Daily -ScheduleTime "02:00" ` 
-OperatorName "DBA-Team" 
} | Select-Object SqlInstance, JobName, Status, ScheduleDesc | Format-Table

New-dtcAgRepairJob -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-ScheduleType Daily -ScheduleTime "04:00" ` 
-Update

New-dtcAgRepairJob -SqlInstance "SQL01" -ScheduleType Manual

# Einmalig mit Admin-Rechten (z. B. im Onboarding-Skript): 
New-EventLog -Source 'dtcSqlTools' -LogName Application 
 
# Job erstellen - EventLog wird automatisch erkannt und verwendet 
New-dtcAgRepairJob -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod"

New-dtcAgRepairJob -SqlInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" ` 
-ScheduleType Daily -ScheduleTime "02:00" -WhatIf

Sync-dtcAgNode

Sync-dtcAgNode -Instance "SQL01"

# Nur Logins 
Sync-dtcAgNode -Instance "SQL01" -ExcludeType Jobs,LinkedServers,Operators,Alerts 
 
# Nur Jobs und Logins 
Sync-dtcAgNode -Instance "SQL01" -ExcludeType LinkedServers,Operators,Alerts

Sync-dtcAgNode -Instance "SQL01" -AvailabilityGroup "AG_Prod"

$r = Sync-dtcAgNode -Instance "SQL01" -Verbose 
$r | Group-Object ObjectType | 
ForEach-Object { 
"$($_.Name): $(@($_.Group|Where Status -eq 'Success').Count) OK, " + 
"$(@($_.Group|Where Status -eq 'Failed').Count) Fehler" 
}

Sync-dtcAgNode -Instance "SQL01" -ExcludeSystemObjects

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\DB_Transfer_Prod"

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\Backup" ` 
-DatabaseFile "MyApp_20260417.bak" ` 
-SQLInstance "SQL01\PROD"

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\Transfer" ` 
-PreRestoreBackup

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\Transfer" ` 
-PreRestoreBackup ` 
-PreRestoreBackupDir "G:\Rollback_Backups"

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\Transfer" ` 
-ContinueOnLoginError

Restore-dtcAlwaysOnDB -Database "MyApp" ` 
-RestoreDir "F:\Transfer" -WhatIf

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp.bak"

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp.bak" ` 
-TargetDatabase "MyApp_Test"

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp_Full.bak" ` 
-DiffBackupFile "F:\Backup\MyApp_Diff.bak"

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp_Full.bak" ` 
-DiffBackupFile @("F:\Backup\MyApp_Diff1.bak","F:\Backup\MyApp_Diff2.bak")

# Windows-User ohne Server-Login auf der Zielinstanz werden automatisch entfernt. 
# Kein Parameter nötig - die Erkennung erfolgt über Get-DbaLogin. 
Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\ProdDB.bak" ` 
-PreRestoreBackup

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp.bak" ` 
-RelocateDataPath "E:\SQLData" ` 
-RelocateLogPath "F:\SQLLogs"

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp.bak" ` 
-KeepOriginalPaths

$results = "F:\Bak\DB1.bak","F:\Bak\DB2.bak","F:\Bak\DB3.bak" | 
Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-ContinueOnError 
 
$results | Format-Table TargetDatabase, UsersRestored, UsersRemoved, Status

Invoke-dtcDatabaseRestore -SqlInstance "SQL01" ` 
-BackupFile "F:\Backup\MyApp.bak" ` 
-PreRestoreBackup 
# Backup landet automatisch in: <SQLBackupDir>\PreRestore\MyApp_PreRestore_<datum>.bak

$r = Invoke-dtcSaObfuscation -SqlInstance "SQL01" 
Write-Host "Neuer Name : $($r.NewLoginName)" 
Write-Host "Kennwort : $($r.GeneratedPassword)" # SOFORT sicher verwahren! 
Write-Host "Sysadmins : $($r.SysadminAccounts -join ', ')"

$r = Invoke-dtcSaObfuscation -SqlInstance "SQL01" -NewName "dbsvc_system"

$r = Invoke-dtcSaObfuscation -SqlInstance "SQL01" ` 
-NewName "msssa" -PasswordLength 24

$r = Invoke-dtcSaObfuscation -SqlInstance "SQL01" 
if ($r.Status -eq 'Success') { 
$r.GeneratedPassword | Out-File "C:\Secure\sa_pw_$(Get-Date -f 'yyyyMMdd').txt" ` 
-Encoding UTF8 
Write-Host "Kennwort gesichert." 
}

$instances = "SQL01","SQL02","SQL03" 
$results = $instances | ForEach-Object { 
Invoke-dtcSaObfuscation -SqlInstance $_ -ContinueOnError 
} 
$results | Select-Object SqlInstance, OriginalLoginName, NewLoginName, 
SysadminCheck, Status | Format-Table -AutoSize

Invoke-dtcSaObfuscation -SqlInstance "SQL01" -WhatIf

Get-dtcSysadminAccounts -SqlInstance "SQL01"

Get-dtcSysadminAccounts -SqlInstance "SQL01" -ExcludeSysAccounts

$r = Get-dtcSysadminAccounts -SqlInstance "SQL01" -ExcludeSysAccounts 
# BUILTIN\Administrators immer separat prüfen 
$r | Where-Object Status -eq 'BuiltinAdmins' | Format-Table SqlInstance, LoginName, IsEnabled 
# Alle Befunde die Aufmerksamkeit erfordern 
$r | Where-Object Status -in 'BuiltinAdmins','Unexpected' | Format-Table

Get-dtcSysadminAccounts -SqlInstance "SQL01" ` 
-ExcludeSysAccounts ` 
-ExcludeLogin "DOMAIN\DBA_Admins","msssa"

Get-dtcSysadminAccounts -SqlInstance "SQL01" ` 
-ExcludeLogin "NT SERVICE\*","NT AUTHORITY\*","DOMAIN\DBA_Admins","msssa"

$exclude = "NT SERVICE\*","NT AUTHORITY\*","CONTOSO\SQLAdmins","msssa" 
Get-dtcSysadminAccounts -SqlInstance "SQL01","SQL02","SQL03" ` 
-ExcludeLogin $exclude -ContinueOnError

$r = Get-dtcSysadminAccounts -SqlInstance "SQL01" ` 
-ExcludeLogin "NT SERVICE\*","msssa" 
$r | Where-Object Status -eq 'Unexpected' | 
Format-Table LoginName, LoginType, IsEnabled, CreateDate, LastLogin

$r = Get-dtcSysadminAccounts -SqlInstance "SQL01" 
$r | Where-Object Status -eq 'Disabled' | 
Select-Object LoginName, LoginType, CreateDate | 
Format-Table -AutoSize

$allInst = "SQL01","SQL02","SQL03","SQL04" 
$all = Get-dtcSysadminAccounts -SqlInstance $allInst -ExcludeLogin "NT SERVICE\*","msssa" -ContinueOnError 
$all | Group-Object SqlInstance | ForEach-Object { 
$u = @($_.Group | Where-Object Status -eq 'Unexpected').Count 
"$($_.Name): $($_.Count) gesamt, $u unerwartet$(if($u -gt 0){' ← PRÜFEN'})" 
}

Invoke-dtcLoginAudit -SqlInstance "SQL01"

Invoke-dtcLoginAudit -SqlInstance "SQL01" ` 
-ExcludeLogin "NT SERVICE\*","NT AUTHORITY\*","msssa" ` 
-InactivityThresholdDays 60 ` 
-MaxPasswordAgeDays 90

Invoke-dtcLoginAudit -SqlInstance "SQL01" ` 
-ExcludeLogin "NT SERVICE\*" ` 
-CheckAdOrphans

$r = Invoke-dtcLoginAudit -SqlInstance "SQL01" -ExcludeLogin "NT SERVICE\*" 
$r | Where-Object Category -eq 'Policy' | 
Format-Table LoginName, FindingType, Detail

$r = Invoke-dtcLoginAudit -SqlInstance "SQL01" 
$r | Where-Object FindingType -in 'Inactive','NeverUsed' | 
Sort-Object { $_.Detail } | 
Format-Table LoginName, LoginType, IsEnabled, Detail

Invoke-dtcLoginAudit -SqlInstance "SQL01","SQL02" ` 
-ExcludeLogin "NT SERVICE\*","msssa" ` 
-ContinueOnError | 
Where-Object FindingType -ne 'Clean' | 
Export-Csv "C:\Reports\LoginAudit_$(Get-Date -f 'yyyyMMdd').csv" ` 
-Encoding UTF8 -NoTypeInformation

Test-dtcSqlConfiguration -SqlInstance "SQL01"

Test-dtcSqlConfiguration -SqlInstance "SQL01" ` 
-AllowCLR -AllowDatabaseMail

Test-dtcSqlConfiguration -SqlInstance "SQL01" ` 
-AllowMixedMode

Test-dtcSqlConfiguration -SqlInstance "SQL01" ` 
-MinCostThresholdParallelism 50

$r = Test-dtcSqlConfiguration -SqlInstance "SQL01" 
$r | Where-Object Status -eq 'Warning' | 
Format-Table Setting, CurrentValue, ExpectedValue, Detail

$r = Test-dtcSqlConfiguration -SqlInstance "SQL01" 
$fixes = $r | Where-Object { $_.Status -in 'Warning','Info' -and $_.FixScript } 
$fixes | ForEach-Object { 
"-- $($_.Setting) (aktuell: $($_.CurrentValue))" 
$_.FixScript 
"" 
} | Out-File "C:\Temp\SQL01_Config_Fix.sql" -Encoding UTF8

$instances = "SQL01","SQL02","SQL03" 
$all = Test-dtcSqlConfiguration -SqlInstance $instances -ContinueOnError 
$all | Group-Object SqlInstance | ForEach-Object { 
$w = @($_.Group|Where Status -eq 'Warning').Count 
"$($_.Name): $w Warnungen" 
}

Test-dtcSsasPermissions

Test-dtcSsasPermissions -Fix

Test-dtcSsasPermissions -ComputerName "SSAS01" -Fix

Test-dtcSsasPermissions -ComputerName "SSAS01","SSAS02" -Fix ` 
-AdditionalAccounts "DOMAIN\DBA_Admins","DOMAIN\SSAS_Backup"

Test-dtcSsasPermissions -ComputerName "SSAS01" -InstanceName "TABULAR" -Fix

Test-dtcSsasPermissions -ComputerName "SSAS01" | 
Where-Object Status -in 'Missing','PathNotFound','Error' | 
Format-Table ComputerName, Instance, InstanceType, PathType, Path, MissingAccounts -AutoSize

Test-dtcSsasPermissions -ComputerName "SSAS01" ` 
-RequiredRights Modify -Fix

"SSAS01","SSAS02","SSAS03" | Test-dtcSsasPermissions -Fix | 
Group-Object ComputerName | 
ForEach-Object { 
$ok = @($_.Group | Where-Object Status -eq 'OK').Count 
$fix = @($_.Group | Where-Object Status -eq 'Fixed').Count 
$err = @($_.Group | Where-Object Status -in 'Missing','Error').Count 
"$($_.Name): $ok OK, $fix gesetzt, $err Problem(e)" 
}

Set-dtcSqlPolicyState -SqlInstance "SQL01" -State Disable

Set-dtcSqlPolicyState -SqlInstance "SQL01" ` 
-Policy "xp_cmdshell must be disabled" -State Disable

"SQL01","SQL02","SQL03" | 
Set-dtcSqlPolicyState -State Enable

$pol = Get-DbaPolicy -SqlInstance "SQL01" -Policy "New Login_Enforce Passwort Policy" 
if (-not $pol.Enabled) { 
Set-dtcSqlPolicyState -SqlInstance "SQL01" -State Enable 
Write-Host "Policy reaktiviert." 
}

Get-dtcDatabaseHealth -SqlInstance "SQL01"

Get-dtcDatabaseHealth -SqlInstance "SQL01" ` 
-MaxCheckDbAgeDays 7 -MaxVlfCount 100

Get-dtcDatabaseHealth -SqlInstance "SQL01" -IncludeSystemDatabases $true

Get-dtcDatabaseHealth -SqlInstance "SQL01" ` 
-ExcludeDatabase "ReportServer*","tempdb"

Get-dtcDatabaseHealth -SqlInstance "SQL01" | 
Where-Object OverallStatus -ne 'OK' | 
Sort-Object OverallStatus, Database | 
Format-Table Database, OverallStatus, CheckDbAgeDays, VlfCount, AutoGrowthEvents -AutoSize

Get-dtcDatabaseHealth -SqlInstance "SQL01" | 
Where-Object { $_.VlfCount -gt 500 } | 
Sort-Object VlfCount -Descending | 
Select-Object Database, VlfCount, SizeMB, RecoveryModel | 
Format-Table -AutoSize

Get-dtcWaitStatReport -SqlInstance "SQL01"

Get-dtcWaitStatReport -SqlInstance "SQL01" ` 
-DeltaMeasurementSeconds 60

Get-dtcWaitStatReport -SqlInstance "SQL01" ` 
-MinWaitTimePct 10 -TopN 5

$r = Get-dtcWaitStatReport -SqlInstance "SQL01" 
$r | Group-Object Category | 
ForEach-Object { 
$ms = ($_.Group | Measure-Object WaitTimeMs -Sum).Sum 
[PSCustomObject]@{ 
Kategorie = $_.Name 
WaitTimeMs = $ms 
WaitTypes = $_.Count 
} 
} | Sort-Object WaitTimeMs -Descending | Format-Table -AutoSize

Get-dtcWaitStatReport -SqlInstance "SQL01","SQL02" -ContinueOnError | 
Where-Object Status -in 'Critical','Warning' | 
Select-Object SqlInstance, WaitType, Category, PctOfTotal, Recommendation | 
Format-Table -AutoSize

# Alle 2 Minuten einen neuen 30-Sekunden-Snapshot 
for ($i=0; $i -lt 10; $i++) { 
Write-Host "=== Messung $($i+1) - $(Get-Date -f 'HH:mm:ss') ===" -ForegroundColor Cyan 
Get-dtcWaitStatReport -SqlInstance "SQL01" ` 
-DeltaMeasurementSeconds 30 -TopN 5 -MinWaitTimePct 5 | 
Format-Table WaitType, Category, PctOfTotal, AvgWaitMs, Status -AutoSize 
Start-Sleep 90 
}

Get-dtcAutoGrowthReport -SqlInstance "SQL01"

Get-dtcAutoGrowthReport -SqlInstance "SQL01" ` 
-HistoryDays 14 -MinGrowthEvents 5

Get-dtcAutoGrowthReport -SqlInstance "SQL01" ` 
-ExcludeDatabase "ReportServer*","model"

$r = Get-dtcAutoGrowthReport -SqlInstance "SQL01" 
$r | Where-Object Status -in 'Critical','Warning' | 
ForEach-Object { 
"-- $($_.Database) | $($_.FileName)" 
"-- Aktuelle Größe: $($_.CurrentSizeMB) MB | Wachstum: $($_.TotalGrowthMB) MB | Empfehlung: $($_.RecommendedSizeMB) MB" 
"ALTER DATABASE [$($_.Database)] MODIFY FILE (NAME = '<LogicalName>', SIZE = $($_.RecommendedSizeMB)MB);" 
"" 
}

Get-dtcAutoGrowthReport -SqlInstance "SQL01" | 
Where-Object FileType -eq 'Log' | 
Sort-Object GrowthEventCount -Descending | 
Format-Table Database, GrowthEventCount, TotalGrowthMB, AvgGrowthDurationMs, Status

Get-dtcDiskSpaceReport -SqlInstance "SQL01"

Get-dtcDiskSpaceReport -SqlInstance "SQL01" ` 
-WarnThresholdPct 30 -CriticalThresholdPct 15

Get-dtcDiskSpaceReport -SqlInstance "SQL01" -HistoryDays 7

Get-dtcDiskSpaceReport -SqlInstance "SQL01" | 
Where-Object Status -in 'Critical','Warning' | 
Format-Table MountPoint, TotalGB, FreeGB, FreePct, DaysUntilFull, Message

Get-dtcDiskSpaceReport -SqlInstance "SQL01","SQL02","SQL03" -ContinueOnError | 
Sort-Object FreePct | 
Format-Table SqlInstance, MountPoint, FreeGB, FreePct, DaysUntilFull, Status

Get-dtcOperationProgress -SqlInstance "SQL01"

Get-dtcOperationProgress -SqlInstance "SQL01" -OperationType Restore

Get-dtcOperationProgress -SqlInstance "SQL01" ` 
-OperationType Restore -Loop -IntervalSeconds 30

Get-dtcOperationProgress -SqlInstance "SQL01" -Database "MyApp" -Loop

# Parallel zu Add-dtcAgDatabase ausführen 
Get-dtcOperationProgress -SqlInstance "SQL01" ` 
-OperationType AutoSeed -Loop -IntervalSeconds 15

Test-dtcBackupCoverage -SqlInstance "SQL01"

Test-dtcBackupCoverage -SqlInstance "SQL01" ` 
-MaxFullBackupAgeDays 1 -MaxLogBackupAgeHours 1

Test-dtcBackupCoverage -SqlInstance "SQL01" ` 
-MaxFullBackupAgeDays 7 ` 
-MaxDiffBackupAgeDays 1 ` 
-MaxLogBackupAgeHours 2

Test-dtcBackupCoverage -SqlInstance "SQL01" ` 
-ExcludeDatabase "ReportServer*","AutomatedTest_*"

Test-dtcBackupCoverage -SqlInstance "SQL01" | 
Where-Object Status -in 'Critical','Missing' | 
Select-Object Database, BackupType, LastBackup, AgeHours, Message | 
Format-Table -AutoSize

$issues = "SQL01","SQL02","SQL03" | 
ForEach-Object { 
Test-dtcBackupCoverage -SqlInstance $_ -ContinueOnError 
} | Where-Object Status -in 'Critical','Missing' 
 
if ($issues) { 
Write-Warning "$(@($issues).Count) Backup-Probleme gefunden!" 
$issues | Format-Table SqlInstance, Database, BackupType, AgeHours, Message 
}

Invoke-dtcAgentJobAudit -SqlInstance "SQL01"

Invoke-dtcAgentJobAudit -SqlInstance "SQL01" -NoFix

Invoke-dtcAgentJobAudit -SqlInstance "SQL01" ` 
-ExcludeDisabledCheck "syspolicy_*","DatabaseBackup_*","IndexOptimize_*"

Invoke-dtcAgentJobAudit -SqlInstance "SQL01" ` 
-ExcludeOwnerFix "ThirdPartyApp_*","Monitoring_*"

Invoke-dtcAgentJobAudit -SqlInstance "SQL01" -HistoryDays 7

$r = Invoke-dtcAgentJobAudit -SqlInstance "SQL01" -NoFix 
$r | Where-Object FindingType -eq 'Failed' | 
Sort-Object Value -Descending | 
Select-Object JobName, Value, Detail | 
Format-Table -AutoSize

$r = Invoke-dtcAgentJobAudit -SqlInstance "SQL01" -NoFix 
$r | Where-Object FindingType -eq 'AvgRuntime' | 
Sort-Object { [int]($_.Detail -replace 'Läufe.*Ø\s*','') -replace '[^0-9].*','' } -Descending | 
Select-Object -First 5 JobName, Value, Detail | 
Format-Table -AutoSize

New-dtcOlaBackupJobs -SqlInstance "SQL01"

New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff

New-dtcOlaBackupJobs -SqlInstance "SQL01" ` 
-BackupDirectory "E:\SQLBackup" ` 
-CleanupTime 72 ` 
-OperatorName "DBA-Team" ` 
-UseDiff

Set-dtcSqlToolsConfig ` 
-OlaJobNameFull "Prod-DB-FULL" ` 
-OlaJobNameDiff "Prod-DB-DIFF" ` 
-OlaJobNameLog "Prod-DB-LOG" 
 
New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff

New-dtcOlaBackupJobs -SqlInstance "SQL01" ` 
-Databases "AppDB,ReportDB" ` 
-LogIntervalMinutes 15 ` 
-CleanupTime 0

New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff -Update

$r = New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff 
Write-Host "FULL : $($r.FullJobStatus)" 
Write-Host "DIFF : $($r.DiffJobStatus)" 
Write-Host "LOG : $($r.LogJobStatus)" 
Write-Host "Dir : $($r.BackupDirectory)"

New-dtcOlaSysDbBackupJob -SqlInstance "SQL01"

# User-Datenbanken (Full/Diff/Log) 
New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff -OperatorName "DBA-Team" 
# System-Datenbanken (täglich Full) 
New-dtcOlaSysDbBackupJob -SqlInstance "SQL01" -OperatorName "DBA-Team"

New-dtcOlaSysDbBackupJob -SqlInstance "SQL01" ` 
-BackupDirectory "E:\SQLBackup" ` 
-ScheduleTime "20:00" ` 
-CleanupTime 72 ` 
-OperatorName "DBA-Team"

Set-dtcSqlToolsConfig -OlaJobNameSysDbBackup "Prod-SysDB-FULL" 
New-dtcOlaSysDbBackupJob -SqlInstance "SQL01"

New-dtcOlaSysDbBackupJob -SqlInstance "SQL01" -ScheduleTime "22:00" -Update

New-dtcOlaMaintenanceJobs -SqlInstance "SQL01"

New-dtcOlaMaintenanceJobs -SqlInstance "SQL01" ` 
-ScheduleTime "22:00" ` 
-ScheduleDay 64

New-dtcOlaMaintenanceJobs -SqlInstance "SQL01" ` 
-FragmentationLevel1 10 ` 
-FragmentationLevel2 40 ` 
-FillFactor 85 ` 
-OperatorName "DBA-Team"

New-dtcOlaMaintenanceJobs -SqlInstance "SQL01" -PhysicalOnly Y

Set-dtcSqlToolsConfig ` 
-OlaJobNameIndexOpt "Prod-IndexOpt" ` 
-OlaJobNameIntUserDb "Prod-Integrity-User" ` 
-OlaJobNameIntSysDb "Prod-Integrity-System" 
New-dtcOlaMaintenanceJobs -SqlInstance "SQL01"

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
# Backup-Jobs 
New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff -OperatorName "DBA-Team" 
# Wartungs-Jobs 
New-dtcOlaMaintenanceJobs -SqlInstance "SQL01" -OperatorName "DBA-Team"

$r = New-dtcOlaMaintenanceJobs -SqlInstance "SQL01" 
Write-Host "IndexOpt : $($r.IndexOptStatus)" 
Write-Host "Int User : $($r.IntUserDbStatus)" 
Write-Host "Int Sys : $($r.IntSysDbStatus)" 
Write-Host "Status : $($r.OverallStatus)"

$export = Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" ` 
-Database "MyApp" -ExportOnly 
$export.ExportedUsers | Format-Table UserName, LoginName, Roles

Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" -Database "MyApp"

# Schritt 1: vor dem Restore exportieren 
$export = Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" ` 
-Database "MyApp" -ExportOnly 
 
# ... eigener Restore-Vorgang ... 
 
# Schritt 2: nach dem Restore bereinigen und reimportieren 
$result = Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" ` 
-Database "MyApp" -SavedUsers $export.ExportedUsers 
Write-Host "$($result.UsersRemoved) entfernt, $($result.UsersRestored) wiederhergestellt"

Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" ` 
-Database "MyApp" -SavedUsers $savedUsers -SkipCleanup

'AppDB','LogDB','AuditDB' | ForEach-Object { 
Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" -Database $_ -Verbose 
} | Select-Object Database, UsersRemoved, Status | Format-Table

Invoke-dtcDatabaseUserCleanup -SqlInstance "SQL01" ` 
-Database "MyApp" -SkipOrphanRepair

Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "Latin1_General_CI_AS"

Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "Latin1_General_CI_AS" -WhatIf

Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "Latin1_General_CI_AS" ` 
-IncludeUserDatabases

Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "Latin1_General_CI_AS" ` 
-IncludeUserDatabases ` 
-ExcludeDatabase "ReportServer*","ThirdPartyApp"

Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "SQL_Latin1_General_CP1_CI_AS" ` 
-BackupBeforeChange ` 
-IncludeUserDatabases

Invoke-dtcCollationChange -SqlInstance "SQL01\INST2" ` 
-NewCollation "Latin1_General_CI_AS"

$r = Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "Latin1_General_CI_AS" ` 
-IncludeUserDatabases 
 
switch ($r.Status) { 
'Success' { 
Write-Host "Erfolgreich: $($r.OldCollation) → $($r.NewCollation)" 
Write-Host "User-DBs geändert: $($r.UserDatabasesChanged)" 
if ($r.ColumnScriptPath) { 
Write-Warning "Spalten-Skript prüfen: $($r.ColumnScriptPath)" 
} 
} 
'AlreadySet' { Write-Host "Bereits korrekt gesetzt." } 
'AgDetected' { Write-Error "AG erkannt – manuell austragen: $($r.Message)" } 
'WhatIf' { Write-Host "WhatIf-Modus – keine Änderungen." } 
default { Write-Error "Fehler: $($r.Status) – $($r.Message)" } 
}

# Rollback-Datei zeigt den exakten Befehl: 
Get-Content "C:\System\WinSrvLog\MSSQL\CollationChange_Rollback_SQL01_2026-04-17.txt" 
 
# Rollback ausführen: 
Invoke-dtcCollationChange -SqlInstance "SQL01" ` 
-NewCollation "SQL_Latin1_General_CP1_CI_AS"

Invoke-dtcLogShrink -SqlInstance "SQL01" -Database "MyApp"

Invoke-dtcLogShrink -SqlInstance "SQL01" -Database "MyApp" ` 
-ShrinkTargetPercent 5 -MinTargetMB 128

Invoke-dtcLogShrink -SqlInstance "SQL01" -All

Invoke-dtcLogShrink -SqlInstance "SQL01" ` 
-Database "MyApp","AppLog","AppAudit"

$r = Invoke-dtcLogShrink -SqlInstance "SQL01" -All 
$r | Select-Object Database, LogSizeMBBefore, LogSizeMBAfter, 
FreedMB, TargetMB, Status | 
Format-Table -AutoSize

Set-dtcSsrsConfiguration

Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" ` 
-DatabaseServer "SQL01" ` 
-Credential (Get-Credential)

Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" ` 
-DatabaseServer "AG_SSRS_Listener" ` 
-DatabaseName "ReportServer"

$svcPwd = Read-Host "Dienstkonto-Kennwort" -AsSecureString 
$keyPwd = Read-Host "Schluessel-Kennwort" -AsSecureString 
Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" ` 
-DatabaseServer "AG_SSRS_Listener" ` 
-ServiceAccount "DOMAIN\svc_ssrs" ` 
-ServiceAccountPassword $svcPwd ` 
-EncryptionKeyFile "\\FileServer\Backup\SSRS_Key.snk" ` 
-EncryptionKeyPassword $keyPwd

Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" ` 
-DatabaseServer "SQL01" ` 
-DatabaseAuthType SQL ` 
-DatabaseCredential (Get-Credential -Message "SQL-Login ReportServer-DB")

Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" ` 
-ReportServerUrl "http://+:8080/ReportServer" ` 
-ReportsUrl "http://+:8080/Reports" ` 
-SkipDatabase -SkipServiceAccount -SkipEncryptionKeyBackup

$r = Set-dtcSsrsConfiguration ` 
-ComputerName "SSRS01" -DatabaseServer "AG_SSRS_Listener" 
Write-Host "Modus : $($r.SsrsMode)" 
Write-Host "DB-Server : $($r.DatabaseServer)" 
Write-Host "AG-Listener: $($r.IsAgListener) | Primary: $($r.AgPrimaryNode)" 
Write-Host "Status : $($r.OverallStatus)" 
Write-Host "Bericht : $($r.ReportPath)"

Set-dtcSqlToolsConfig ` 
-CentralLogPath "W:\75084-Datenbanken\MSSQL\dtc\Log" ` 
-KeyPath "W:\75084-Datenbanken\MSSQL\dtc\Keys"

Set-dtcSqlToolsConfig ` 
-OlaJobNameFull "Prod-FULL" ` 
-OlaJobNameDiff "Prod-DIFF" ` 
-OlaJobNameLog "Prod-LOG"

Set-dtcSqlToolsConfig | Format-List

Set-dtcTsmConfiguration -UseDiff

Set-dtcTsmConfiguration -ManagementClass MC_B_NL.NL_62.62.NA

Set-dtcTsmConfiguration ` 
-ComputerName "SQL01" ` 
-ManagementClass MC_B_NL.NL_42.42.NA

Set-dtcTsmConfiguration ` 
-ComputerName "SQL01" ` 
-BackupDirectory "E:\SQLBackup" ` 
-ManagementClass MC_B_NL.NL_42.42.NA ` 
-AdditionalIncludePaths @("E:\SQLBackup\Archive","F:\TSMLogs")

Set-dtcTsmConfiguration ` 
-DsmOptPath "C:\TSM\config\dsm.opt" ` 
-ManagementClass MC_B_NL.NL_35.35.NA

# 1. Ola Backup-Jobs anlegen 
New-dtcOlaBackupJobs -SqlInstance "SQL01" -UseDiff 
New-dtcOlaSysDbBackupJob -SqlInstance "SQL01" 
 
# 2. TSM für dieselben Backup-Verzeichnisse konfigurieren 
Set-dtcTsmConfiguration -ComputerName "SQL01" -UseDiff

Set-dtcTsmConfiguration -ComputerName "SQL01" -UseDiff -WhatIf

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
Set-dtcSsisConfiguration -SqlInstance "SQL01" -CatalogPassword $pwd

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
$svcPwd = Read-Host "Dienst-Kennwort" -AsSecureString 
Set-dtcSsisConfiguration ` 
-SqlInstance "SQL01" ` 
-CatalogPassword $pwd ` 
-SsisServiceAccount "DOMAIN\svc_ssis" ` 
-SsisServiceAccountPassword $svcPwd ` 
-CatalogFolder @("ETL","Staging","Reporting") ` 
-Environments @("Development","Test","Production") ` 
-RetentionPeriod 180

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
Set-dtcSsisConfiguration ` 
-SqlInstance "SQL01" ` 
-AgName "AG_SSIS" ` 
-AgListener "AG_SSIS_Listener" ` 
-CatalogPassword $pwd ` 
-CatalogFolder "ETL"

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
$svcPwd = Read-Host "Dienst-Kennwort" -AsSecureString 
Set-dtcSsisConfiguration ` 
-SqlInstance "SQL01" ` 
-AgName "AG_SSIS" ` 
-AgNodes @("SQL01","SQL02","SQL03") ` 
-CatalogPassword $pwd ` 
-SsisServiceAccount "DOMAIN\svc_ssis" ` 
-SsisServiceAccountPassword $svcPwd ` 
-CatalogFolder @("ETL","Staging") ` 
-Environments @("DEV","TEST","PROD")

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
Set-dtcSsisConfiguration ` 
-SqlInstance "SQL01" ` 
-CatalogPassword $pwd ` 
-CatalogFolder @("Finance","Logistics") ` 
-Environments @("Production") ` 
-SkipService -SkipAg

$pwd = Read-Host "SSISDB-Kennwort" -AsSecureString 
$r = Set-dtcSsisConfiguration ` 
-SqlInstance "SQL01" -AgName "AG_SSIS" -CatalogPassword $pwd 
Write-Host "Status : $($r.OverallStatus)" 
Write-Host "AG-Nodes : $($r.AgNodes -join ', ')" 
Write-Host "Listener : $($r.AgListener)" 
Write-Host "Ordner neu : $($r.FoldersCreated -join ', ')" 
Write-Host "Umgebungen : $($r.EnvironmentsCreated -join ', ')" 
Write-Host "Bericht : $($r.ReportPath)"

Test-dtcSqlSpn

Test-dtcSqlSpn -SqlInstance "SQL01"

Test-dtcSqlSpn -SqlInstance "SQL01\PROD"

Test-dtcSqlSpn -SqlInstance "SQL01","SQL02","SQL03"

Test-dtcSqlSpn -SqlInstance "SQL01" | 
Where-Object Status -in 'Missing','WrongAccount' | 
Select-Object SPN, Status, ExpectedAccount, SetSpnCommand, DeleteSpnCommand | 
Format-Table -AutoSize

Test-dtcSqlSpn -SqlInstance "SQL01","SQL02","SQL03" | 
Where-Object Status -in 'Missing','WrongAccount' | 
ForEach-Object { 
if ($_.DeleteSpnCommand) { "# Schritt 1 - falsches Konto entfernen:"; $_.DeleteSpnCommand } 
"# Schritt 2 - SPN registrieren:"; $_.SetSpnCommand; "" 
} | Out-File "C:\Temp\SPN_Befehle_AD_Team.txt" -Encoding UTF8

Export-dtcInstanceInventory -SqlInstance "SQL01"

Export-dtcInstanceInventory -SqlInstance "SQL01","SQL02","SQL03" ` 
-ContinueOnError

$r = Export-dtcInstanceInventory -SqlInstance "SQL01" 
Write-Host "TXT: $($r.TxtFile)" 
Write-Host "CSV: $($r.CsvFile)" 
# Direkt öffnen: 
Start-Process notepad $r.TxtFile

$r = Export-dtcInstanceInventory -SqlInstance "SQL01" 
# CSV enthält: DatabaseName, Status, RecoveryModel, SizeMB, LastFullBackup, Owner, ... 
Import-Csv $r.CsvFile | Sort-Object SizeMB -Descending | 
Select-Object DatabaseName, SizeMB, RecoveryModel, LastFullBackup | 
Format-Table -AutoSize

Export-dtcInstanceInventory -SqlInstance "SQL01","SQL02","SQL03" -ContinueOnError | 
Select-Object SqlInstance, Version, Edition, DatabaseCount, JobCount | 
Format-Table -AutoSize

Set-dtcSqlToolsSharePath -Path '\\FileServer\IT_Tools\dtcSqlTools'

# Als SYSTEM oder lokaler Admin auf allen Zielrechnern ausführen: 
Import-Module '\\FileServer\IT_Tools\dtcSqlTools' 
Set-dtcSqlToolsSharePath -Path '\\FileServer\IT_Tools\dtcSqlTools' 
# Ab jetzt: jeder Import-Module-Aufruf auf diesen Rechnern prüft auf Updates

Update-dtcSqlTools -ShowSharePath

Set-dtcSqlToolsSharePath -Remove

# Pro-Session (kein Adminrecht nötig): 
$env:DTCSQLTOOLS_SHARE = '\\FileServer\IT_Tools\dtcSqlTools' 
 
# Dauerhaft für alle Benutzer (Adminrecht nötig): 
[System.Environment]::SetEnvironmentVariable( 
'DTCSQLTOOLS_SHARE', 
'\\FileServer\IT_Tools\dtcSqlTools', 
'Machine')

Update-dtcSqlTools -CheckOnly

Update-dtcSqlTools

Update-dtcSqlTools -Force

Update-dtcSqlTools -SharePath '\\MGMT-SRV\Modules\dtcSqlTools'

Update-dtcSqlTools -ShowSharePath

$r = Update-dtcSqlTools 
switch ($r.Status) { 
'Updated' { Write-Host "Update auf v$($r.NewVersion) erfolgreich." -ForegroundColor Green } 
'UpToDate' { Write-Host "Bereits aktuell (v$($r.LocalVersion))." } 
'ShareNotReachable'{ Write-Warning "Share nicht erreichbar: $($r.Message)" } 
'NoShareConfigured'{ Write-Warning "Kein Share konfiguriert – Set-dtcSqlToolsSharePath verwenden." } 
'Failed' { Write-Error "Update fehlgeschlagen: $($r.Message)" } 
}

Remove-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "MyApp" -AvailabilityGroup "AG_Prod"

Remove-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "MyApp","AppLog" -AvailabilityGroup "AG_Prod"

Reset-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "MyApp" -AvailabilityGroup "AG_Prod"

# Erst prüfen was repariert werden müsste 
Repair-dtcAgDatabase -Instance "SQL01" -WhatIf 
 
# Dann tatsächlich reparieren 
Repair-dtcAgDatabase -Instance "SQL01" -ContinueOnError

Reset-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-AvailabilityGroup "AG_Prod" -All

# Problem-Status vorher prüfen 
Get-DbaAgDatabase -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" | 
Where-Object SynchronizationState -ne 'Synchronized' | 
Select-Object Name, SynchronizationState, SynchronizationHealth 
 
# Dann gezielt zurücksetzen 
Reset-dtcAgDatabase -PrimaryInstance "SQL01" ` 
-DatabaseName "MyApp" -AvailabilityGroup "AG_Prod" -Verbose

Get-dtcMonitoringKey

Get-dtcMonitoringKey -ComputerName "SQL01","SQL02","SQL03" | 
Format-Table ComputerName, SQL, SQL_Description, SQLFreeSpaceVersion, TSM, Status

Set-dtcMonitoringKey -Operation Set -SQL Full -TSM 1 ` 
-SQLFreeSpaceVersion Cluster ` 
-ComputerName "SQL01","SQL02","SQL03"

Set-dtcMonitoringKey -Operation Set -SQL Standard -TSM 1 ` 
-SQLFreeSpaceVersion Standard ` 
-ComputerName "SQLSA01"

Set-dtcMonitoringKey -Operation Set -SQL Full -TSM 1 ` 
-AutoDetectSQLFreeSpaceVersion ` 
-ComputerName "SQL01"

Set-dtcMonitoringKey -Operation Set -SQL None -TSM 0 ` 
-ComputerName "SQL01"

Set-dtcMonitoringKey -Operation Set -SQL Full -TSM 1 ` 
-RegistryBase "Software\OEM" ` 
-ComputerName "SQL01" 
# Schreibt nach: HKLM:\Software\OEM\FITS\SystemCenter