#!/usr/bin/env python3 r""" Q1 Волна A: цикл BACKUP -> verify -> hash -> upload ch12 -> download-check -> del. Пилотный цикл отработан на 2610BERRYOILUAP02 (TRAINING_BACKUP_PLAN.md §9). Правила: - строго одна БД за раз - del на сервере ТОЛЬКО после сверки SHA256 скачанного файла - при любой ошибке файл на сервере НЕ удаляется, статус пишется в манифест Манифест: schema_inventory/q1_waveA_manifest.tsv """ import re import subprocess import sys import time from datetime import datetime from pathlib import Path SQLCMD = [ "docker", "run", "--rm", "--network", "host", "mcr.microsoft.com/mssql/server:2022-latest", "/opt/mssql-tools18/bin/sqlcmd", "-S", "50.21.183.111", "-U", "irely", "-P", "iRely486", "-C", "-d", "master", "-s", "|", "-W", ] CH12 = "https://ch12.hostserviceapp.com" BAKDIR = r"D:\irelyinstall\backup" LOCAL = Path("/root/ir-assessment/redteam/irelydata/schema_inventory/waveA_baks") MANIFEST = Path("/root/ir-assessment/redteam/irelydata/schema_inventory/q1_waveA_manifest.tsv") WAVE_A = [ "2610PAMDALEUAP01", "2430ProdDevPalmdaleUAP01", "2710PALMDALEOILUAP01", "PALMDALEOILUAP01", "2610CHERRYENERGYUAP01", "2710DALLMYRUAP01", "2610RTROGERSUAP01", ] LOCAL.mkdir(exist_ok=True) def sql(query: str, timeout: int = 590) -> str: p = subprocess.run(SQLCMD + ["-Q", query], capture_output=True, text=True, timeout=timeout) out = p.stdout junk = ("SQL Server 2022 will run", "This container is running", "go.microsoft") out = "\n".join(l for l in out.splitlines() if not any(j in l for j in junk)) return out def log(msg: str): ts = datetime.now().strftime("%H:%M:%S") print(f"[{ts}] {msg}", flush=True) # серверная дата для имени файла srv = sql("SET NOCOUNT ON; SELECT CONVERT(varchar(8), GETDATE(), 112)") m = re.search(r"(\d{8})", srv) if not m: log("FATAL: не получил серверную дату") sys.exit(1) d8 = m.group(1) # YYYYMMDD suffix = f"{d8[4:6]}{d8[6:8]}{d8[0:4]}" # MMDDYYYY как у них log(f"Серверная дата: {d8} -> суффикс имён _{suffix}") rows = [] with MANIFEST.open("a") as mf: if mf.tell() == 0: mf.write("db\tbak_name\tsize_bytes\tsha256\turl\tupload_s\tverified\tdeleted\tstatus\n") for db in WAVE_A: bak = f"{db}_{suffix}.bak" bakpath = f"{BAKDIR}\\{bak}" log(f"=== {db} -> {bak} ===") status, size, sha, url, up_s, verified, deleted = "FAIL", "", "", "", "", "no", "no" try: # 0) коллизия имён chk = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo EXISTS) else (echo FREE)'") if "EXISTS" in chk: status = "SKIP_NAME_COLLISION" log(f" {db}: файл уже существует — пропускаю (не перезаписываю чужое)") rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue # 1) BACKUP + VERIFYONLY + SHA256 + размер t0 = time.time() out = sql( f"SET NOCOUNT ON;" f"BACKUP DATABASE [{db}] TO DISK = N'{bakpath}' WITH COMPRESSION, COPY_ONLY, INIT, CHECKSUM, STATS=25;" f"RESTORE VERIFYONLY FROM DISK = N'{bakpath}' WITH CHECKSUM;" f"EXEC xp_cmdshell 'certutil -hashfile \"{bakpath}\" SHA256';" f"EXEC xp_cmdshell 'for %F in (\"{bakpath}\") do @echo SIZE=%~zF';", timeout=590) bt = time.time() - t0 if "successfully processed" not in out or "is valid" not in out: status = "FAIL_BACKUP_OR_VERIFY" log(f" {db}: BACKUP/VERIFY проблема:\n{out[-500:]}") rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue mh = re.search(r"\b([0-9a-f]{64})\b", out) ms = re.search(r"SIZE=(\d+)", out) sha = mh.group(1) if mh else "" size = ms.group(1) if ms else "" log(f" {db}: BACKUP ok за {bt:.0f}s, size={size}, sha256={sha[:16]}...") if not sha or not size: status = "FAIL_NO_HASH_OR_SIZE" rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue # 2) UPLOAD (1 попытка + 1 ретрай) url = "" for attempt in (1, 2): t0 = time.time() out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{bakpath}\" {CH12}'", timeout=590) up_s = f"{time.time()-t0:.0f}" mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() break log(f" {db}: upload попытка {attempt} неудачна: {out[:200]}") time.sleep(10) if not url: status = "FAIL_UPLOAD" rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue log(f" {db}: uploaded за {up_s}s -> {url}") # 3) DOWNLOAD + hash check (локально) local_f = LOCAL / bak dl = subprocess.run(["curl", "-sS", "-o", str(local_f), url], capture_output=True, text=True, timeout=590) if dl.returncode != 0 or not local_f.exists(): status = "FAIL_DOWNLOAD" log(f" {db}: download fail: {dl.stderr[:200]}") rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue lh = subprocess.run(["sha256sum", str(local_f)], capture_output=True, text=True).stdout.split()[0] ls = local_f.stat().st_size if lh != sha or str(ls) != size: status = "FAIL_HASH_MISMATCH" log(f" {db}: HASH/SIZE MISMATCH local={lh[:16]}/{ls} server={sha[:16]}/{size} — файл на сервере НЕ удаляю") rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() continue verified = "yes" log(f" {db}: verified (sha256 совпал, {ls} bytes)") # 4) DEL на сервере (только после verified) out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'del \"{bakpath}\"'; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo STILL_THERE) else (echo GONE)'") deleted = "yes" if "GONE" in out else "NO" if deleted != "yes": status = "WARN_DEL_FAILED" log(f" {db}: del не сработал! {out[:200]}") else: status = "OK" log(f" {db}: OK, серверный файл удалён") except subprocess.TimeoutExpired: status = "FAIL_TIMEOUT" log(f" {db}: TIMEOUT — файл на сервере не трогаю") except Exception as e: status = f"FAIL_EXC:{type(e).__name__}" log(f" {db}: {e}") rows.append((db, bak, size, sha, url, up_s, verified, deleted, status)) mf.write("\t".join([db, bak, size, sha, url, up_s, verified, deleted, status]) + "\n"); mf.flush() log("=== ВОЛНА A ЗАВЕРШЕНА ===") oks = sum(1 for r in rows if r[8] == "OK") log(f"OK: {oks}/{len(rows)}") for r in rows: if r[8] != "OK": log(f" НЕ-OK: {r[0]} -> {r[8]}")