Created
June 2, 2023 11:46
-
-
Save materro/1710a84347f4115d1e40b4cb09023549 to your computer and use it in GitHub Desktop.
MS SQL Server backup script in Powershell
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Configure the MS SQL Server | |
| $backupRootFolder = "C:\Backup" | |
| $mainDbName = "DATABASE-NAME" | |
| $serverName = "SERVER-NAME" | |
| $daysOfBackup = 7 | |
| $queryTimeout = 120 | |
| $dayOfFullBackup = 'Friday' | |
| # Main script | |
| $dayOfWeek = (Get-Date).DayOfWeek | |
| $backupSubfolder = "$backupRootFolder\$(Get-Date -Format yyyyMMdd)-$dayOfWeek" | |
| $backupDeleteDay = (Get-Date).AddDays(-($daysOfBackup)) | |
| $backupDeleteDayFull = $backupDeleteDay.AddDays(-(([int]$backupDeleteDay.DayOfWeek + 7 - [int][DayOfWeek]::$dayOfFullBackup) % 7)) | |
| try { | |
| # List all databases | |
| $databases = Invoke-Sqlcmd -ServerInstance $serverName -Database $mainDbName ` | |
| -Query " | |
| SELECT name, recovery_model_desc | |
| FROM sys.databases | |
| WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') | |
| ORDER BY 1" | |
| # Remove empty directories | |
| Get-ChildItem -Path $backupRootFolder -Directory -Recurse | | |
| Where-Object { $_.GetFileSystemInfos().Count -eq 0 } | | |
| Remove-Item -Force -Recurse | |
| } catch { | |
| Write-Host "Backup error: $($_.Exception.Message)" | |
| exit 1 | |
| } | |
| $exitStatus = 0 | |
| # Iterate over the database names using foreach | |
| foreach ($db in $databases) { | |
| $dbName = $db.name | |
| $recoveryModel = $db.recovery_model_desc | |
| if ($recoveryModel -eq "SIMPLE") { | |
| Write-Host "Database '$dbName' is in SIMPLE recovery model - switching to FULL..." | |
| Invoke-Sqlcmd -QueryTimeout $queryTimeout -ServerInstance $serverName -Database $mainDbName ` | |
| -Query "ALTER DATABASE $dbName SET RECOVERY FULL" | |
| } | |
| Write-Host "Backup '$backupSubfolder' for '$dbName' started..." | |
| try { | |
| if (-not (Test-Path $backupSubfolder)) { | |
| New-Item -ItemType Directory -Path $backupSubfolder | |
| } | |
| # Full backup will be created once a week | |
| $filename = "${dbName}_FUL_$(Get-Date -Format yyyyMMdd).bak" | |
| if (-not (Test-Path $filename) -and $dayOfWeek -eq $dayOfFullBackup) { | |
| Invoke-Sqlcmd -QueryTimeout $queryTimeout -ServerInstance $serverName -Database $dbName ` | |
| -Query "BACKUP DATABASE $dbName TO DISK = '$backupSubfolder\$filename' WITH COMPRESSION, CHECKSUM" | |
| } | |
| $filename = "${dbName}_DIF_$(Get-Date -Format yyyyMMdd_HHmm).bak" | |
| Invoke-Sqlcmd -QueryTimeout $queryTimeout -ServerInstance $serverName -Database $dbName ` | |
| -Query "BACKUP DATABASE $dbName TO DISK = '$backupSubfolder\$filename' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM" | |
| $filename = "${dbName}_LOG_$(Get-Date -Format yyyyMMdd_HHmm).trn" | |
| Invoke-Sqlcmd -QueryTimeout $queryTimeout -ServerInstance $serverName -Database $dbName ` | |
| -Query "BACKUP LOG $dbName TO DISK = '$backupSubfolder\$filename' WITH COMPRESSION, CHECKSUM" | |
| # Remove old backups | |
| Get-ChildItem -Path $backupRootFolder -File | | |
| Where-Object { | |
| $_.Name -like "${dbName}_*" -and $_.LastWriteTime.Date -lt $backupDeleteDayFull.Date | |
| } | Remove-Item -Force | |
| } catch { | |
| Write-Host "Backup of '$dbName' failed: $($_.Exception.Message)" | |
| $exitStatus = 1 | |
| } | |
| } | |
| Write-Host "Backup end..." | |
| exit $exitStatus |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment