#!/usr/bin/env python3 r""" Q1 Волна C: 2430DDENERGYUAP101 (192.9 GB live: mdf 132.8 + ldf 60.0). Схема как волна B (указание оператора): BACKUP -> VERIFYONLY -> certutil SHA256 -> upload ch12 -> БЕЗ скачивания к нам -> del на сервере. Контрольная сумма -> q1_checksums.tsv (wave=C). Отличия от B: увеличенные таймауты (backup 2ч, upload 8ч) + preflight по месту на D:. Их собственный .bak этой БД весил 1.09 GB при 193 GB live — аномалия; ожидаем наш FULL с COMPRESSION ~15-30 GB (по пилотному коэффициенту 8-10x от mdf). """ 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" SI = Path("/root/ir-assessment/redteam/irelydata/schema_inventory") CHECKSUMS = SI / "q1_checksums.tsv" WAVE_C = ["2430DDENERGYUAP101"] MIN_FREE_GB = 60 # запас места на D: под .bak def sql(query: str, timeout: int = 590) -> str: p = subprocess.run(SQLCMD + ["-Q", query], capture_output=True, text=True, timeout=timeout) junk = ("SQL Server 2022 will run", "This container is running", "go.microsoft") return "\n".join(l for l in p.stdout.splitlines() if not any(j in l for j in junk)) def log(msg: str): print(f"[{datetime.now().strftime('%H:%M:%S')}] {msg}", flush=True) # --- preflight: место на D: --- out = sql("SET NOCOUNT ON; EXEC xp_cmdshell 'wmic logicaldisk get caption,freespace,size'") free_d = None for line in out.splitlines(): m = re.match(r"D:\s+(\d+)\s+\d+", line.strip()) if m: free_d = int(m.group(1)) / 1024**3 if free_d is None: log("FATAL: не удалось определить свободное место D:") sys.exit(1) log(f"Preflight: свободно на D: {free_d:.0f} GB (требуется >= {MIN_FREE_GB})") if free_d < MIN_FREE_GB: log("FATAL: недостаточно места — волна C не запущена") sys.exit(1) 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) suffix = f"{d8[4:6]}{d8[6:8]}{d8[0:4]}" log(f"Серверная дата {d8} -> суффикс _{suffix}") if not CHECKSUMS.exists(): CHECKSUMS.write_text("wave\tdb\tbak_name\tsize_bytes\tsha256\turl\tverification\n") with CHECKSUMS.open("a") as cf: results = [] for db in WAVE_C: bak = f"{db}_{suffix}.bak" bakpath = f"{BAKDIR}\\{bak}" log(f"=== {db} -> {bak} ===") status, size, sha, url = "FAIL", "", "", "" try: 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}: имя уже существует — пропускаю (не перезаписываю)") results.append((db, bak, size, sha, url, status)) cf.write(f"C\t{db}\t{bak}\t\t\t\t{status}\n"); cf.flush() continue # 1) BACKUP (до 2 ч) + VERIFYONLY + hash + size t0 = time.time() out = sql( f"SET NOCOUNT ON;" f"BACKUP DATABASE [{db}] TO DISK = N'{bakpath}' WITH COMPRESSION, COPY_ONLY, INIT, CHECKSUM, STATS=10;" 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=7200) 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 fail:\n{out[-800:]}") results.append((db, bak, size, sha, url, status)) cf.write(f"C\t{db}\t{bak}\t\t\t\t{status}\n"); cf.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} ({int(size or 0)/1024**3:.2f} GB), sha256={sha[:16]}...") if not sha or not size: status = "FAIL_NO_HASH_OR_SIZE" results.append((db, bak, size, sha, url, status)) cf.write(f"C\t{db}\t{bak}\t{size}\t{sha}\t\t{status}\n"); cf.flush() continue # 2) UPLOAD (до 8 ч, 1 ретрай) for attempt in (1, 2): t0 = time.time() try: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{bakpath}\" {CH12}'", timeout=28800) except subprocess.TimeoutExpired: out = "UPLOAD_TIMEOUT" up_s = time.time() - t0 mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() log(f" {db}: uploaded за {up_s:.0f}s -> {url}") break log(f" {db}: upload попытка {attempt} неудачна ({up_s:.0f}s): {out[:200]}") time.sleep(30) if not url: status = "FAIL_UPLOAD" results.append((db, bak, size, sha, url, status)) cf.write(f"C\t{db}\t{bak}\t{size}\t{sha}\t\t{status}\n"); cf.flush() continue # 3) del на сервере (после успешного upload; сверка хеша — на ch12 оператором) out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'del \"{bakpath}\"'; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo STILL_THERE) else (echo GONE)'", timeout=590) deleted = "GONE" in out status = "OK_PENDING_CH12_CHECKSUM" if deleted else "WARN_DEL_FAILED" log(f" {db}: {status}") except subprocess.TimeoutExpired: status = "FAIL_TIMEOUT" log(f" {db}: TIMEOUT — файл на сервере не трогаю") except Exception as e: status = f"FAIL_EXC:{type(e).__name__}:{e}" log(f" {db}: {e}") results.append((db, bak, size, sha, url, status)) cf.write(f"C\t{db}\t{bak}\t{size}\t{sha}\t{url}\t{status}\n"); cf.flush() log("=== ВОЛНА C ЗАВЕРШЕНА ===") oks = sum(1 for r in results if r[5].startswith("OK")) log(f"OK: {oks}/{len(results)}") for r in results: if not r[5].startswith("OK"): log(f" НЕ-OK: {r[0]} -> {r[5]}")