#!/usr/bin/env python3 r""" Q1 Волна B: BACKUP -> VERIFYONLY -> certutil SHA256 -> upload ch12 -> ЗАПИСЬ ХЕША -> del. ОТЛИЧИЕ от волны A (по указанию оператора): файл обратно НЕ скачиваем. Контрольные суммы пишутся в q1_checksums.tsv; оператор сверит их на стороне ch12. del на сервере — только после получения URL от curl (upload success). Таймауты увеличены: бэкапы 10-25 GB, выгрузка ~2.6 MB/s. """ 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" MANIFEST_A = SI / "q1_waveA_manifest.tsv" WAVE_B = [ "2610BERRYOILUAP01", # 22.1 GB live "CHERRYENERGYUAP01", # 20.7 GB live "2610PIGOTTOILUAP01", # 20.6 GB live "2610JOHNSONPETROLEUMUAP01", # 24.3 GB live ] 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) # --- инициализация файла контрольных сумм: переносим волну A (download-verified) --- new_file = not CHECKSUMS.exists() with CHECKSUMS.open("a") as cf: if new_file: cf.write("wave\tdb\tbak_name\tsize_bytes\tsha256\turl\tverification\n") if MANIFEST_A.exists(): for line in MANIFEST_A.read_text().splitlines()[1:]: f = line.split("\t") if len(f) == 9 and f[8] == "OK": cf.write(f"A\t{f[0]}\t{f[1]}\t{f[2]}\t{f[3]}\t{f[4]}\tdownload-verified\n") log("Файл контрольных сумм создан, волна A перенесена (7 строк, download-verified)") 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}") results = [] for db in WAVE_B: 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"B\t{db}\t{bak}\t\t\t\t{status}\n"); cf.flush() continue # 1) BACKUP (до 1 ч) + 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=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=3600) 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[-600:]}") results.append((db, bak, size, sha, url, status)) cf.write(f"B\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)/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"B\t{db}\t{bak}\t{size}\t{sha}\t\t{status}\n"); cf.flush() continue # 2) UPLOAD (до 3 ч, 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=10800) 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(15) if not url: status = "FAIL_UPLOAD" results.append((db, bak, size, sha, url, status)) cf.write(f"B\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)'") 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"B\t{db}\t{bak}\t{size}\t{sha}\t{url}\t{status}\n"); cf.flush() log("=== ВОЛНА B ЗАВЕРШЕНА ===") 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]}")