📘 SQL Server Database Sync Documentation

(Current → Backup | Insert-Only | Production Safe)

---

🔹 Documentation ka Purpose

Yeh documentation production support teams ke liye banayi gayi hai jahan aksar yeh issue hota hai:

• ✔ Ek Live / Current database hoti hai jo application use karti hai
• ✔ Ek Backup / DR database hoti hai
• ❌ Backup database complete sync nahi hoti

Simple words me:
Current DB me records zyada hotay hain, backup DB me kuch data missing hota hai.

Goal:
Backup DB ko Current DB ke barabar lana,
sirf missing records insert karke,
kisi existing data ko touch kiye baghair.

---

🔹 Kab Use Karni Chahiye?

• Current DB ko source of truth maana ja raha ho
• Backup DB me records kam hon
• Dono DB ka schema same ho
• Aap DELETE / UPDATE nahi karna chahte

✔ Common Real-Life Scenarios

• SQL Agent job fail ho gayi
• Partial backup restore ho gaya
• Server crash ke baad verification
• Audit / reporting mismatch

---

❌ Kab Use NA Karein

• Agar dono DBs actively write ho rahi hon
• Agar bi-directional sync chahiye
• Agar delete ya overwrite karna ho

---

🔹 Architecture (Concept)

Current Database (Source of Truth)
        |
        |  Linked Server (OPENQUERY)
        |
Backup Database (Read-Only Copy)

Rule: Data ka flow hamesha Current → Backup

---

🔹 Step 1: Linked Server Create Karna

EXEC sp_addlinkedserver
    @server   = 'BK135',
    @provider = 'MSOLEDBSQL',
    @datasrc  = '192.168.31.1**';

EXEC sp_addlinkedsrvlogin
    @rmtsrvname  = 'BK135',
    @useself     = 'false',
    @rmtuser     = 'link_user',
    @rmtpassword= '********';

Roman English:
Linked server ke baghair OPENQUERY kaam nahi karega.

---

🔹 Step 2: Temporary Permission (Backup DB)

USE OrderBookingDB;
ALTER ROLE db_owner ADD MEMBER [link_user];

Sync complete hone ke baad zaroor remove karein:

ALTER ROLE db_owner DROP MEMBER [link_user];

---

🔹 Step 3: Table-Wise Record Comparison

Yeh query batati hai kaun si table me data missing hai:

;WITH cur AS (
    SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS TotalRows
    FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id=t.schema_id
    JOIN sys.partitions p ON p.object_id=t.object_id
    WHERE p.index_id IN (0,1)
    GROUP BY s.name, t.name
),
bak AS (
    SELECT * FROM OPENQUERY(BK135, '
        SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS TotalRows
        FROM cl_dbname.sys.tables t
        JOIN cl_dbname.sys.schemas s ON s.schema_id=t.schema_id
        JOIN cl_dbname.sys.partitions p ON p.object_id=t.object_id
        WHERE p.index_id IN (0,1)
        GROUP BY s.name, t.name
    ')
)
SELECT
    cur.TableName,
    cur.TotalRows AS CurrentRows,
    bak.TotalRows AS BackupRows,
    cur.TotalRows - bak.TotalRows AS Difference
FROM cur
JOIN bak ON cur.TableName = bak.TableName
WHERE cur.TotalRows  bak.TotalRows;

Difference > 0 → Backup me records missing

---

🔹 Step 4: Single Primary Key Sync

Example: ShopBase (PK = ShopId)

INSERT INTO OPENQUERY(BK135,
'SELECT ShopId, shopname, ShopType, ShopSector
 FROM cl_dbname.dbo.ShopBase')
SELECT
    c.ShopId,
    c.shopname,
    c.ShopType,
    c.ShopSector
FROM dbo.ShopBase c
LEFT JOIN OPENQUERY(BK135,
'SELECT ShopId FROM cl_dbname.dbo.ShopBase') b
ON b.ShopId = c.ShopId
WHERE b.ShopId IS NULL;

---

🔹 Step 5: Composite Key Tables

Example: OrderEntries (OrderID + SkuId)

INSERT INTO OPENQUERY(BK135,
'SELECT OrderID, SkuId, Quantity, Price
 FROM cl_dbname.dbo.OrderEntries')
SELECT
    c.OrderID,
    c.SkuId,
    c.Quantity,
    c.Price
FROM dbo.OrderEntries c
LEFT JOIN OPENQUERY(BK135,
'SELECT OrderID, SkuId FROM cl_dbname.dbo.OrderEntries') b
ON b.OrderID = c.OrderID
AND b.SkuId = c.SkuId
WHERE b.OrderID IS NULL;

---

🔹 Step 6: Identity Tables (Attendance / Logs)

• ❌ Identity column insert na karein
• ✔ Natural data se comparison karein

INSERT INTO OPENQUERY(BK135,
'SELECT UserId, WorkDate, CheckIn
 FROM cl_dbname.dbo.attendance')
SELECT
    UserId,
    WorkDate,
    CheckIn
FROM dbo.attendance c
WHERE NOT EXISTS (
    SELECT 1
    FROM OPENQUERY(BK135,
    'SELECT UserId, WorkDate, CheckIn FROM cl_dbname.dbo.attendance') b
    WHERE
        CONVERT(NVARCHAR(4000),b.UserId)=CONVERT(NVARCHAR(4000),c.UserId)
    AND CONVERT(NVARCHAR(4000),b.WorkDate)=CONVERT(NVARCHAR(4000),c.WorkDate)
    AND CONVERT(NVARCHAR(4000),b.CheckIn)=CONVERT(NVARCHAR(4000),c.CheckIn)
);

---

🔹 Step 7: Large Tables (Batch Insert)

DECLARE @BatchSize INT = 500;
DECLARE @LastId BIGINT = 0;
DECLARE @MaxId BIGINT;

SELECT @MaxId = MAX(Id) FROM dbo.location_tracking;

WHILE @LastId  @LastId
      AND c.Id <= @LastId + @BatchSize;

    SET @LastId += @BatchSize;
END

---

🔹 Final Summary

• ✔ Insert-only (safe)
• ✔ No data overwrite
• ✔ Identity & composite key safe
• ✔ ERP / CRM / Attendance systems ke liye ideal

Bottom Line:
Yeh approach production-tested, predictable aur support-friendly hai.