conatum.net

# Importieren des SqlServer Moduls
Import-Module SqlServer

# Server-Instanz festlegen
$serverInstance = "SQLServer"

# Backup-Verzeichnis festlegen
$backupDirectory = "D:\MSSQL\Backup"

# Verbindung zum SQL Server herstellen
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverInstance

# Alle Datenbanken durchgehen, außer Systemdatenbanken
$server.Databases | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object {
    $dbName = $_.Name
    $timestamp = Get-Date -Format yyyyMMddHHmmss
    $backupFileName = "$backupDirectory\$dbName" + "_$timestamp.bak"

    # Backup-Objekt erstellen
    $backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
    $backup.BackupSetDescription = "Vollständiges Backup von $dbName"
    $backup.BackupSetName = "$dbName Backup"
    $backup.Database = $dbName
    $backup.MediaDescription = "Disk"
    $backup.Devices.AddDevice($backupFileName, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $backup.SqlBackup($server)
    Write-Output "Backup von $dbName wurde erstellt: $backupFileName"
}