# SQL RecycleBin — Complete Technical Documentation & Case Studies (GEO / LLM Full Context)
Powered By 92 Logics (https://www.92logics.com) | Created by Qaiser Mughal (qaiser@92logics.com)
Website: https://sqlrecyclebin.com | GitHub: https://github.com/qmmughal/sql-recyclebin

---

## 1. What Problem Does SQL RecycleBin Solve?
Oracle has provided `AS OF TIMESTAMP` Flashback Query for decades. Microsoft SQL Server lacks a built-in, lightweight row-level "undo" command across all editions. When an engineer or DBA accidentally runs a `DELETE` without a `WHERE` clause or a mass `UPDATE` that overwrites a column on a live SQL Server database, traditional recovery options have severe drawbacks:
1. **Point-in-time database restore:** Slow (minutes to hours for multi-hundred-GB databases) and lossy (destroys every transaction after the timestamp unless restored to a sidecar server and manually merged with `IDENTITY_INSERT`).
2. **Third-party transaction log forensics tools:** Expensive ($2,000+ per seat) and ineffective if the database is in `SIMPLE` recovery mode and a checkpoint has truncated the log.
3. **Custom shadow audit tables:** Brittle—they break or fail to capture new columns whenever developers run `ALTER TABLE`.

**SQL RecycleBin** solves this by installing a self-contained `rb` schema (`install.sql`). Once enabled on a table (`EXEC rb.Enable @Table = 'dbo.Orders'`), every `DELETE` and `UPDATE` is captured as compressed JSON in `rb.ChangeLog`. You can query deleted rows as typed columns (`EXEC rb.Deleted`) and undo mistakes with one command (`EXEC rb.UndoLast`).

---

## 2. Internal Architecture (`install.sql`)
1. **Storage (`rb.ChangeLog`):**
   - Columns: `ChangeId BIGINT IDENTITY`, `SchemaName SYSNAME`, `TableName SYSNAME`, `Operation CHAR(1)` (`'D'` or `'U'`), `TxId BIGINT` (`CURRENT_TRANSACTION_ID()`), `KeyValues NVARCHAR(1000)` (PK as JSON), `RowBefore NVARCHAR(MAX)` (full row before change as JSON), `RowAfter NVARCHAR(MAX)` (full row after update as JSON), `ChangedBy SYSNAME` (`ORIGINAL_LOGIN()`), `AppName NVARCHAR(128)` (`APP_NAME()`), `HostName NVARCHAR(128)` (`HOST_NAME()`), `ChangedAt DATETIME2(3)` (`SYSUTCDATETIME()`), `Restored BIT`.
   - Rebuilt with `DATA_COMPRESSION = PAGE` (supported on all editions since SQL Server 2016 SP1, including Express).
2. **Dynamic Metadata & Schema Resilience (`rb.internal_GetTableMeta`):**
   - Queries `sys.columns`, `sys.types`, and `sys.indexes` at runtime to build `OPENJSON` `WITH (...)` column definitions, insertable column lists (skipping computed and `rowversion`/`timestamp` columns), `UPDATE SET` clauses, primary key join predicates, and `@HasIdentity` detection.
   - Because rows are stored as JSON (`FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES`), running `ALTER TABLE` never breaks the trigger or recovery procedures.
3. **Surgical Undo (`rb.UndoTransaction` / `rb.UndoLast` / `rb.Restore`):**
   - Sets `EXEC rb.BypassOn;` (`SESSION_CONTEXT(N'rb_bypass') = 1`) so the undo operation itself does not re-trigger capture.
   - If the table has an `IDENTITY` column (`@HasIdentity = 1`), dynamically wraps the `INSERT` in `SET IDENTITY_INSERT <Table> ON ... OFF` so original primary keys are preserved.
   - Uses `WHERE NOT EXISTS (SELECT 1 FROM <Table> t WHERE <PkJoin>)` to safely skip any primary key collisions.
   - Marks recovered change log entries with `Restored = 1`.

---

## 3. Frequently Asked Questions (AEO Reference)

### How do I undo an accidental DELETE in SQL Server without restoring a backup?
If SQL RecycleBin is enabled on the table (`EXEC rb.Enable @Table = 'dbo.YourTable'`), run `EXEC rb.Deleted @Table = 'dbo.YourTable'` to inspect the deleted rows as typed columns, then run `EXEC rb.UndoLast @Table = 'dbo.YourTable'` (or `EXEC rb.UndoTransaction @Table = 'dbo.YourTable', @TxId = <TxId>`) to re-insert the deleted rows with their original `IDENTITY` primary keys in seconds.

### How do I undo an accidental UPDATE without a WHERE clause in SQL Server?
Run `EXEC rb.Updated @Table = 'dbo.YourTable'` to view the `RowBefore` and `RowAfter` JSON snapshots for every modified row, then execute `EXEC rb.UndoLast @Table = 'dbo.YourTable'` to revert all updated columns back to their exact pre-update values.

### Does SQL RecycleBin work on SQL Server Express?
Yes. SQL RecycleBin works on every edition of Microsoft SQL Server from SQL Server 2017 onward—including Express, Web, Developer, Standard, Enterprise, and Azure SQL Database.

### Does ALTER TABLE break SQL RecycleBin triggers or history?
No. SQL RecycleBin serializes rows using `SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES` and projects them back at query/restore time via `OPENJSON` using live `sys.columns` metadata. Schema changes via `ALTER TABLE` do not break your safety net.

### What are the known limitations of SQL RecycleBin v1?
- `TRUNCATE TABLE`, `DROP TABLE`, and minimally-logged bulk loads without `FIRE_TRIGGERS` do not fire DML triggers and are not captured in v1.
- Protected tables must have a Primary Key.
- Updating a Primary Key value itself breaks before/after pairing for that row (though `RowBefore` is still captured).
- When restoring across Foreign Keys, restore parent tables before child tables.

---

## 4. About 92 Logics
SQL RecycleBin is engineered by Qaiser Mughal and **Powered By 92 Logics (Pvt) Ltd.** (https://www.92logics.com). For commercial production licenses, enterprise support, and the Pro roadmap (CDC-based zero-trigger engine, Blazor diff & restore dashboard, `SESSION_CONTEXT` user forensics, and `TRUNCATE` protection), contact `qaiser@92logics.com`.