Created
July 26, 2022 13:39
-
-
Save 1k-off/7eef267f215fe2f1d3b4820a8425c4b0 to your computer and use it in GitHub Desktop.
Backup sql databases
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
| Import-Module SqlServer | |
| $DatabaseInstances = @( | |
| "SQL2017", | |
| "SQL2019" | |
| ) | |
| $ServerNameUserFriendly = "web" | |
| $DaysToStore = 4 | |
| $CurrentDate = $(Get-Date -Format "dd-MM-yyyy") | |
| $BackupPathGeneral = "\\u123456.your-storagebox.de\backup\DataBases\$ServerNameUserFriendly\$CurrentDate" | |
| $BackupPathWeekly = "\\u123456.your-storagebox.de\backup\LongTermBackups\Databases\$ServerNameUserFriendly" | |
| $BackupPathLocal = "C:\one-time-backup\$CurrentDate" | |
| $SlackWebHook = "https://hooks.slack.com/services/...." | |
| $SlackChannel = "#admin-alerts" | |
| $WorkingDirectory = "$PSScriptRoot" | |
| $ServerName = "$env:computername" | |
| foreach ($instance in $DatabaseInstances) { | |
| Set-Location "$WorkingDirectory" | |
| if ( -not (Test-Path -LiteralPath "$BackupPathLocal\$instance")) { | |
| New-Item "$BackupPathLocal\$instance" -ItemType Directory | |
| } | |
| if ( -not (Test-Path -LiteralPath "$BackupPathGeneral\$instance")) { | |
| New-Item "$BackupPathGeneral\$instance" -ItemType Directory | |
| } | |
| if ( -not (Test-Path -LiteralPath "$BackupPathWeekly\$instance")) { | |
| New-Item "$BackupPathWeekly\$instance" -ItemType Directory | |
| } | |
| Set-Location "SQLSERVER:\SQL\$ServerName\$instance\Databases" | |
| foreach ($database in (Get-ChildItem)) { | |
| $dbName = $database.Name | |
| Backup-SqlDatabase -Database $dbName -BackupFile "$BackupPathLocal\$instance\$dbName.bak" | |
| } | |
| Get-ChildItem "$BackupPathLocal\$instance" -Filter *.bak | Foreach-Object { | |
| $name = (Get-Item "$BackupPathLocal\$instance\$_").BaseName | |
| Compress-7Zip -Path "$BackupPathLocal\$instance\$_" -ArchiveFileName "${name}.zip" -OutputPath "$BackupPathLocal\$instance" | |
| Remove-Item "$BackupPathLocal\$instance\$_" | |
| } | |
| } | |
| Set-Location "$WorkingDirectory" | |
| if ((Get-Date).DayOfWeek -eq "Sunday") { | |
| Copy-Item -Path "$BackupPathLocal\*" -Destination "$BackupPathWeekly" -Recurse -Force | |
| } | |
| Move-Item -Path "$BackupPathLocal\*" -Destination "$BackupPathGeneral" -Force | |
| $reportMessage = "" | |
| # Check and report | |
| foreach ($instance in $DatabaseInstances) { | |
| if ((Get-Date).DayOfWeek -eq "Sunday") { | |
| if ( -not (Test-Path $BackupPathWeekly\$instance -PathType Container)) { | |
| $reportMessage = $reportMessage + "Seems, that backup for Hetzner\$ServerNameUserFriendly\$instance do not exist. `n" | |
| } else { | |
| $count = (Get-ChildItem $BackupPathWeekly\$instance).Count | |
| $reportMessage = $reportMessage + "Backup for Hetzner\$ServerNameUserFriendly\$instance exists. Files count: $count. `n" | |
| } | |
| } | |
| if ( -not (Test-Path $BackupPathGeneral\$instance -PathType Container)) { | |
| $reportMessage = $reportMessage + "Seems, that backup for $ServerNameUserFriendly\$instance do not exist. `n" | |
| } else { | |
| $count = (Get-ChildItem $BackupPathGeneral\$instance).Count | |
| $reportMessage = $reportMessage + "Backup for $ServerNameUserFriendly\$instance exists. Files count: $count. `n" | |
| } | |
| } | |
| # Remove old backups | |
| Get-ChildItem -Path "$BackupPathLocal\..\" -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse | |
| $limit = (Get-Date).AddDays(-$DaysToStore) | |
| Get-ChildItem -Path $BackupPathGeneral\..\ -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force | |
| Get-ChildItem -Path $BackupPathGeneral\..\ -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse | |
| Send-SlackMessage -Uri $SlackWebHook ` | |
| -Channel "$SlackChannel" ` | |
| -Parse full ` | |
| -IconEmoji :sql: ` | |
| -AsUser ` | |
| -Username 'Server $ServerNameUserFriendly' ` | |
| -Text "$reportMessage" |
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
| param( | |
| [Parameter(Mandatory)] | |
| [string]$InstanceName, | |
| [Parameter(Mandatory)] | |
| [string]$DatabaseListFile, | |
| [Parameter(Mandatory)] | |
| [string]$BackupPath, | |
| [Parameter(Mandatory)] | |
| [string]$Action | |
| ) | |
| $DatabaseList = [string[]]$arrayFromFile = Get-Content -Path "$DatabaseListFile" | |
| Import-Module SqlServer | |
| $WorkingDirectory = "$PSScriptRoot" | |
| $ServerName = "$env:computername" | |
| switch ($Action) { | |
| Backup { | |
| Set-Location "$WorkingDirectory" | |
| if ( -not (Test-Path -LiteralPath "$BackupPath\$InstanceName")) { | |
| New-Item "$BackupPath\$InstanceName" -ItemType Directory | |
| } | |
| Set-Location "SQLSERVER:\SQL\$ServerName\$InstanceName\Databases" | |
| foreach ($dbName in $DatabaseList) { | |
| Backup-SqlDatabase -Database $dbName -BackupFile "$BackupPath\$InstanceName\$dbName.bak" | |
| } | |
| Set-Location "$WorkingDirectory" | |
| } | |
| Restore { | |
| Set-Location "$WorkingDirectory" | |
| foreach ($dbName in $DatabaseList) { | |
| Restore-SqlDatabase -ServerInstance "$ServerName\$InstanceName" -Database "$dbName" -BackupFile "$BackupPath\$InstanceName\$dbName.bak" -AutoRelocateFile -PassThru | |
| } | |
| } | |
| } |
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
| PS> Install-Module -Name 7Zip4Powershell | |
| PS> Install-Module -Name PSSlack | |
| PS> Install-Module -Name SqlServer -AllowClobber | |
| PS> Import-Module SqlServer |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment