68 lines
2.5 KiB
YAML
68 lines
2.5 KiB
YAML
---
|
|
- name: Find sqlcmd.exe
|
|
ansible.windows.win_powershell:
|
|
script: |
|
|
$sqlcmd = Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Filter 'sqlcmd.exe' `
|
|
-Recurse -ErrorAction SilentlyContinue |
|
|
Where-Object { $_.FullName -notlike '*\x86\*' } |
|
|
Sort-Object LastWriteTime -Descending |
|
|
Select-Object -First 1 -ExpandProperty FullName
|
|
if (-not $sqlcmd) { throw 'sqlcmd.exe not found' }
|
|
$Ansible.Result = $sqlcmd
|
|
$Ansible.Changed = $false
|
|
register: sqlcmd_path
|
|
|
|
- name: Download database backups
|
|
ansible.windows.win_get_url:
|
|
url: "{{ item.url }}"
|
|
dest: "{{ db_backup_dir }}\\{{ item.filename }}"
|
|
loop: "{{ db_backups }}"
|
|
|
|
- name: Restore databases
|
|
ansible.windows.win_powershell:
|
|
script: |
|
|
$ErrorActionPreference = 'Stop'
|
|
|
|
$sqlcmd = '{{ sqlcmd_path.result }}'
|
|
$bakFile = '{{ db_backup_dir }}\{{ item.filename }}'
|
|
$dbName = '{{ item.name }}'
|
|
|
|
# Idempotent: skip if database already exists
|
|
$exists = (& $sqlcmd -S . -E -h -1 `
|
|
-Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name = N'$dbName'" |
|
|
Out-String).Trim()
|
|
if ($exists -eq $dbName) {
|
|
Write-Output "$dbName already exists — skipping restore."
|
|
$Ansible.Changed = $false
|
|
return
|
|
}
|
|
|
|
# Get logical file list from the backup
|
|
$raw = & $sqlcmd -S . -E -s "|" -W `
|
|
-Q "RESTORE FILELISTONLY FROM DISK = N'$bakFile'"
|
|
|
|
$files = $raw | Select-Object -Skip 2 | Where-Object { $_ -match '\|' } | ForEach-Object {
|
|
$cols = $_ -split '\|'
|
|
[PSCustomObject]@{
|
|
LogicalName = $cols[0].Trim()
|
|
PhysicalName = $cols[1].Trim()
|
|
FileType = $cols[2].Trim() # D = data, L = log
|
|
}
|
|
}
|
|
|
|
# Build MOVE clauses — keep original filename, redirect to correct partition
|
|
$moves = $files | ForEach-Object {
|
|
$dir = if ($_.FileType -eq 'L') { '{{ db_log_dir }}' } else { '{{ db_data_dir }}' }
|
|
$file = [System.IO.Path]::GetFileName($_.PhysicalName)
|
|
"MOVE N'$($_.LogicalName)' TO N'$dir\$file'"
|
|
}
|
|
|
|
$sql = "RESTORE DATABASE [$dbName] FROM DISK = N'$bakFile' WITH $($moves -join ', '), REPLACE, STATS = 10"
|
|
Write-Output "Restoring $dbName ..."
|
|
& $sqlcmd -S . -E -Q $sql -t 3600
|
|
if ($LASTEXITCODE -ne 0) { throw "RESTORE failed with exit code $LASTEXITCODE" }
|
|
loop: "{{ db_backups }}"
|
|
loop_control:
|
|
label: "{{ item.name }}"
|
|
timeout: 7200
|