Introduction
Database grow hota rehta hai — aur ek point ke baad performance slow hone lagti hai. Queries heavy ho jati hain, indexing inefficient ho jata hai, aur storage cost bhi badh jati hai.
Is problem ka best solution hai:
👉 Data Archiving
Is blog me hum ek production-level SQL Server archiving system ko samjhenge jo:
Automated hai
Safe hai (data loss prevention)
Scalable hai (batch processing)
Multi-server support karta hai
Aur sabse important 👉 rollback + validation bhi karta hai
Previous Concept Recap
Agar tumne pehle archiving padha hai to basics ye hote hain:
Old data ko main table se hatao
Archive table me move karo
Performance improve karo
But real-world me ye kaafi nahi hota ❌
👉 Hume chahiye:
Data consistency
Logging
Validation
Multi-table support
Cross-server archiving
Main Concept – Stored Procedure Based Archiving Engine
Yaha ek powerful stored procedure use ki gayi hai:
IF OBJECT_ID('[dbo].[usp_RunArchiveProcess]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[usp_RunArchiveProcess];
GO
CREATE PROCEDURE [dbo].[usp_RunArchiveProcess]
@ArchiveID INT = 0 -- 0 = process all; specific ID = process one
,@ArcPassword NVARCHAR(50)='TLPro#123'
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @ArchiveID = 0 SET @ArchiveID = NULL;
-- =========================================================================
-- SECTION 0: Auto-create ArchiveLog
-- =========================================================================
IF OBJECT_ID('dbo.ArchiveLog', 'U') IS NULL
BEGIN
CREATE TABLE dbo.ArchiveLog (
LogID INT IDENTITY(1,1) PRIMARY KEY,
BatchGUID UNIQUEIDENTIFIER NOT NULL,
TableName NVARCHAR(128) NOT NULL,
ArchiveType TINYINT NOT NULL,
Mode TINYINT NOT NULL,
Status NVARCHAR(20) NOT NULL,
RowsMarked INT NULL,
RowsArchived INT NULL,
RowsDeleted INT NULL,
ErrorMsg NVARCHAR(MAX) NULL,
StartedOn DATETIME NOT NULL DEFAULT GETDATE(),
CompletedOn DATETIME NULL
);
PRINT 'ArchiveLog table auto-created.';
END
-- =========================================================================
-- SECTION 1: Read config
-- =========================================================================
DECLARE @ArcServer NVARCHAR(200) = '';
DECLARE @DBName SYSNAME = '';
DECLARE @ArcUsername SYSNAME = '';
SELECT
@ArcServer = MAX(CASE WHEN FieldName = 'DBServerName' THEN Value END),
@DBName = MAX(CASE WHEN FieldName = 'DBName' THEN Value END),
@ArcUsername = MAX(CASE WHEN FieldName = 'Username' THEN Value END)
FROM ScheduledTaskSettings
WHERE SchTaskID = 12
AND FieldName IN ('DBServerName', 'DBName', 'Username');
IF NULLIF(@ArcServer, '') IS NULL
BEGIN
RAISERROR('Config missing: DBServerName (SchTaskID=12)', 16, 1);
RETURN;
END
IF NULLIF(@DBName, '') IS NULL
BEGIN
RAISERROR('Config missing: DBName (SchTaskID=12)', 16, 1);
RETURN;
END
-- =========================================================================
-- SECTION 2: Determine Mode
-- =========================================================================
DECLARE @Mode INT = 0;
DECLARE @SrvName NVARCHAR(200) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(200));
IF @ArcServer = @SrvName AND @DBName = DB_NAME() SET @Mode = 1;
ELSE IF @ArcServer = @SrvName SET @Mode = 2;
ELSE SET @Mode = 3;
PRINT '>> Mode=' + CAST(@Mode AS VARCHAR(5))
+ ' | Server=' + @ArcServer
+ ' | DB=' + @DBName;
IF @Mode = 1
BEGIN
PRINT 'Source = Destination. Nothing to do.';
RETURN;
END
-- =========================================================================
-- SECTION 3: Get ArchiveID range
-- =========================================================================
DECLARE @RowID INT, @MaxID INT;
SELECT
@RowID = MIN(ArchiveID),
@MaxID = MAX(ArchiveID)
FROM ADT_ArchiveData
WHERE ArchiveType IN (1, 2)
AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
IF @RowID IS NULL
BEGIN
PRINT 'No archive configs found.';
RETURN;
END
PRINT 'ArchiveID range: ' + CAST(@RowID AS VARCHAR(10)) + ' to ' + CAST(@MaxID AS VARCHAR(10));
-- =========================================================================
-- SECTION 4: Shared variables
-- =========================================================================
DECLARE
@TableName NVARCHAR(128),
@RefTable NVARCHAR(MAX),
@PrimaryKey NVARCHAR(128),
@DateColumn NVARCHAR(128),
@ArchiveUpTo DATETIME,
@ArchiveType INT,
@ArchiveTable NVARCHAR(128),
@RefArchiveTable NVARCHAR(128),
@Ref NVARCHAR(128),
@SQL NVARCHAR(MAX),
@CheckSQL NVARCHAR(MAX),
@RemoteSQL NVARCHAR(MAX),
@ColumnList NVARCHAR(MAX),
@RefColumnList NVARCHAR(MAX),
@ColListRaw NVARCHAR(MAX),
@RefColListRaw NVARCHAR(MAX),
@BatchId UNIQUEIDENTIFIER,
@BatchSize INT = 5000,
@SrcCnt INT = 0,
@ArcCnt INT = 0,
@RefSrcCnt INT = 0,
@RefArcCnt INT = 0,
@RowsArchived INT = 0,
@RowsDeleted INT = 0,
@Cnt INT = 1,
@SkipTable BIT = 0;
-- =========================================================================
-- #ArchivePKs — direct DDL, procedure scope, SQL 2012 safe
-- NVARCHAR(450): covers INT/BIGINT/UNIQUEIDENTIFIER/VARCHAR PKs safely
-- =========================================================================
IF OBJECT_ID('tempdb..#ArchivePKs') IS NOT NULL DROP TABLE #ArchivePKs;
CREATE TABLE #ArchivePKs (PK_Value NVARCHAR(450) NOT NULL);
CREATE CLUSTERED INDEX IX_PKs ON #ArchivePKs (PK_Value);
-- =========================================================================
-- MODE 2: SAME SERVER, DIFFERENT DATABASE
-- =========================================================================
IF @Mode = 2
BEGIN
IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE name = @DBName)
BEGIN
SET @SQL = N'CREATE DATABASE ' + QUOTENAME(@DBName);
EXEC sp_executesql @SQL;
PRINT 'Destination DB created: ' + @DBName;
END
ELSE PRINT 'Destination DB exists: ' + @DBName;
WHILE @RowID IS NOT NULL AND @RowID <= @MaxID
BEGIN
SELECT
@TableName = NULL, @RefTable = '',
@PrimaryKey = NULL, @DateColumn = NULL,
@ArchiveUpTo = NULL, @ArchiveType = NULL;
SELECT
@ColumnList = '', @RefColumnList = '',
@SrcCnt = 0, @ArcCnt = 0,
@RowsArchived = 0, @RowsDeleted = 0,
@SkipTable = 0;
SET @BatchId = NEWID();
SELECT
@TableName = TableName,
@RefTable = ISNULL(RefTables, ''),
@PrimaryKey = ReferenceID,
@DateColumn = CompareField,
@ArchiveUpTo = ArchiveUpTo,
@ArchiveType = ArchiveType
FROM ADT_ArchiveData
WHERE ArchiveID = @RowID
AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
IF @TableName IS NULL GOTO NextRowMode2;
SET @ArchiveTable = @TableName + '_Archive';
PRINT CHAR(10) + '-- Mode2 | Table: ' + @TableName + ' | Batch: ' + CAST(@BatchId AS NVARCHAR(50));
INSERT INTO dbo.ArchiveLog (BatchGUID, TableName, ArchiveType, Mode, Status)
VALUES (@BatchId, @TableName, @ArchiveType, 2, 'Running');
BEGIN TRY
-- STEP 1: Capture PKs
-- [FIX-1] CAST added — ensures type-safe insert into NVARCHAR(450) column
TRUNCATE TABLE #ArchivePKs;
SET @SQL = '
INSERT INTO #ArchivePKs (PK_Value)
SELECT CAST(' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@DateColumn) + ' <= @ArchiveUpTo;
';
EXEC sp_executesql @SQL, N'@ArchiveUpTo DATETIME', @ArchiveUpTo;
SET @SrcCnt = @@ROWCOUNT;
PRINT ' Rows to archive: ' + CAST(@SrcCnt AS VARCHAR(20));
IF @SrcCnt = 0
BEGIN
UPDATE dbo.ArchiveLog
SET Status='Completed', RowsMarked=0, RowsArchived=0, RowsDeleted=0, CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
PRINT ' Nothing to archive.';
GOTO NextRowMode2;
END
-- STEP 2: Build column list
SET @ColumnList = '';
SELECT @ColumnList = @ColumnList +
QUOTENAME(c.name) + ' ' +
CASE
WHEN t.name IN ('varchar','char','nvarchar','nchar')
THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '(' + CAST(c.precision AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE t.name
END + ' ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TableName)
ORDER BY c.column_id;
IF LEN(@ColumnList) > 0 SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1);
-- STEP 3: Create main archive table in dest DB
SET @SQL = '
IF OBJECT_ID(' + QUOTENAME(@DBName + '.dbo.' + @ArchiveTable, '''') + ',''U'') IS NULL
CREATE TABLE ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + '
(' + @ColumnList + ');
';
EXEC sp_executesql @SQL;
-- STEP 4: Archive main table (ArchiveType=1 only)
IF @ArchiveType = 1
BEGIN
SET @SQL = '
INSERT INTO ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + '
SELECT S.*
FROM ' + QUOTENAME(@TableName) + ' S
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(S.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
WHERE NOT EXISTS (
SELECT 1
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
WHERE A.' + QUOTENAME(@PrimaryKey) + ' = S.' + QUOTENAME(@PrimaryKey) + '
);
';
EXEC sp_executesql @SQL;
SET @RowsArchived = @@ROWCOUNT;
-- STEP 5: Count validation
SET @CheckSQL = '
SELECT @ArcCnt = COUNT(*)
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @CheckSQL, N'@ArcCnt INT OUTPUT', @ArcCnt OUTPUT;
IF @ArcCnt <> @SrcCnt
BEGIN
PRINT ' COUNT MISMATCH: Src=' + CAST(@SrcCnt AS VARCHAR(20)) + ' Arc=' + CAST(@ArcCnt AS VARCHAR(20));
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
UPDATE dbo.ArchiveLog
SET Status='Failed',
ErrorMsg='Count mismatch: Src='+CAST(@SrcCnt AS VARCHAR(20))+' Arc='+CAST(@ArcCnt AS VARCHAR(20)),
CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
SET @SkipTable = 1;
END
END -- ArchiveType=1 main
-- STEP 6: Reference tables
IF @SkipTable = 0 AND @RefTable <> ''
BEGIN
PRINT ' Processing ref tables: ' + @RefTable;
DECLARE RefCursorM2 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value))
FROM dbo.fnGEN_Split(@RefTable, ',')
WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefCursorM2;
FETCH NEXT FROM RefCursorM2 INTO @Ref;
WHILE @@FETCH_STATUS = 0 AND @SkipTable = 0
BEGIN
SET @RefArchiveTable = @Ref + '_Archive';
SET @RefColumnList = '';
SELECT @RefColumnList = @RefColumnList +
QUOTENAME(c.name) + ' ' +
CASE
WHEN t.name IN ('varchar','char','nvarchar','nchar')
THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '(' + CAST(c.precision AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE t.name
END + ' ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@Ref)
ORDER BY c.column_id;
IF LEN(@RefColumnList) > 0 SET @RefColumnList = LEFT(@RefColumnList, LEN(@RefColumnList) - 1);
-- Create ref archive table
SET @SQL = '
IF OBJECT_ID(' + QUOTENAME(@DBName + '.dbo.' + @RefArchiveTable, '''') + ',''U'') IS NULL
CREATE TABLE ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + '
(' + @RefColumnList + ');
';
EXEC sp_executesql @SQL;
IF @ArchiveType = 1
BEGIN
-- [FIX-2] CAST added in all 4 ref JOIN places
SET @SQL = '
INSERT INTO ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + '
SELECT R.*
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
WHERE NOT EXISTS (
SELECT 1
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
WHERE A.' + QUOTENAME(@PrimaryKey) + ' = R.' + QUOTENAME(@PrimaryKey) + '
);
';
EXEC sp_executesql @SQL;
-- [FIX-2] Ref count validation — CAST added
SET @SQL = '
SELECT @RefSrcCnt = COUNT(*)
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL, N'@RefSrcCnt INT OUTPUT', @RefSrcCnt OUTPUT;
SET @CheckSQL = '
SELECT @RefArcCnt = COUNT(*)
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @CheckSQL, N'@RefArcCnt INT OUTPUT', @RefArcCnt OUTPUT;
IF @RefSrcCnt <> @RefArcCnt
BEGIN
PRINT ' REF MISMATCH [' + @Ref + ']: Src=' + CAST(@RefSrcCnt AS VARCHAR(20)) + ' Arc=' + CAST(@RefArcCnt AS VARCHAR(20));
SET @SkipTable = 1;
UPDATE dbo.ArchiveLog
SET Status='Failed',
ErrorMsg='Ref mismatch ['+@Ref+']: Src='+CAST(@RefSrcCnt AS VARCHAR(20))+' Arc='+CAST(@RefArcCnt AS VARCHAR(20)),
CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
END
ELSE PRINT ' Ref archived: ' + @Ref + ' (' + CAST(@RefArcCnt AS VARCHAR(20)) + ' rows)';
END
FETCH NEXT FROM RefCursorM2 INTO @Ref;
END
CLOSE RefCursorM2; DEALLOCATE RefCursorM2;
-- Rollback ref + main if mismatch
IF @SkipTable = 1
BEGIN
DECLARE RefRBM2 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value)) FROM dbo.fnGEN_Split(@RefTable, ',') WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefRBM2; FETCH NEXT FROM RefRBM2 INTO @Ref;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RefArchiveTable = @Ref + '_Archive';
-- [FIX-2] CAST added
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Ref rollback done: ' + @Ref;
FETCH NEXT FROM RefRBM2 INTO @Ref;
END
CLOSE RefRBM2; DEALLOCATE RefRBM2;
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Main archive rollback done: ' + @TableName;
END
END -- ref tables
-- STEP 7: Delete from SOURCE
IF @SkipTable = 0 AND @ArchiveType IN (1, 2)
BEGIN
IF @RefTable <> ''
BEGIN
DECLARE RefDelM2 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value)) FROM dbo.fnGEN_Split(@RefTable, ',') WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefDelM2; FETCH NEXT FROM RefDelM2 INTO @Ref;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
DELETE R
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Ref deleted: ' + @Ref + ' (' + CAST(@@ROWCOUNT AS VARCHAR(20)) + ' rows)';
FETCH NEXT FROM RefDelM2 INTO @Ref;
END
CLOSE RefDelM2; DEALLOCATE RefDelM2;
END
SET @SQL = '
DELETE S
FROM ' + QUOTENAME(@TableName) + ' S
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(S.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
SET @RowsDeleted = @@ROWCOUNT;
PRINT ' Source deleted: ' + CAST(@RowsDeleted AS VARCHAR(20)) + ' rows from ' + @TableName;
UPDATE dbo.ArchiveLog
SET Status='Completed', RowsMarked=@SrcCnt, RowsArchived=@RowsArchived, RowsDeleted=@RowsDeleted, CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
PRINT ' DONE: ' + @TableName;
END
END TRY
BEGIN CATCH
IF CURSOR_STATUS('local','RefCursorM2') >= 0 BEGIN CLOSE RefCursorM2; DEALLOCATE RefCursorM2; END
IF CURSOR_STATUS('local','RefRBM2') >= 0 BEGIN CLOSE RefRBM2; DEALLOCATE RefRBM2; END
IF CURSOR_STATUS('local','RefDelM2') >= 0 BEGIN CLOSE RefDelM2; DEALLOCATE RefDelM2; END
UPDATE dbo.ArchiveLog
SET Status='Failed', ErrorMsg=ERROR_MESSAGE(), CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId AND TableName=@TableName;
PRINT ' ERROR [' + @TableName + ']: ' + ERROR_MESSAGE();
EXECUTE usp_ErrorDB_AddNew;
END CATCH
NextRowMode2:
SELECT @RowID = MIN(ArchiveID)
FROM ADT_ArchiveData
WHERE ArchiveID > @RowID
AND ArchiveType IN (1, 2)
AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
END -- WHILE Mode2
END -- Mode 2
-- =========================================================================
-- MODE 3: DIFFERENT SERVER (Linked Server)
--
-- [FIX-3] Rollback strategy changed:
-- BEFORE: EXEC('DELETE ... JOIN #ArchivePKs') AT @LS
-- → WRONG: #ArchivePKs is LOCAL, remote server cannot see it
-- AFTER: DELETE via 4-part naming locally with #ArchivePKs JOIN
-- → CORRECT: runs on local engine, accesses remote via linked server,
-- #ArchivePKs is visible because query executes locally
-- Same pattern already used for COUNT validation (which works fine)
-- =========================================================================
IF @Mode = 3
BEGIN
DECLARE @LS NVARCHAR(200) = NULL;
DECLARE @LSDB NVARCHAR(400) = NULL;
DECLARE @LSReturnCode INT = 0;
DECLARE @LSErrorMessage NVARCHAR(2000) = '';
EXEC usp_LinkedServiceManager
@Action = 1,
@Password = @ArcPassword,
@IsCreateLocalLS = 0,
@LocalPassword = '',
@OutLinkedServer = @LS OUTPUT,
@OutLinkedServerDB = @LSDB OUTPUT,
@OutReturnCode = @LSReturnCode OUTPUT,
@OutErrorMessage = @LSErrorMessage OUTPUT;
PRINT 'Linked Server: ' + ISNULL(@LS, 'NULL');
PRINT 'Linked Server DB: ' + ISNULL(@LSDB, 'NULL');
IF @LSReturnCode <> 0
BEGIN
RAISERROR('Linked Server creation failed: %s', 16, 1, @LSErrorMessage);
RETURN;
END
IF ISNULL(@LS, '') = ''
BEGIN
RAISERROR('Linked Server not found.', 16, 1);
RETURN;
END
IF OBJECT_ID('tempdb..#DBCheck') IS NOT NULL DROP TABLE #DBCheck;
CREATE TABLE #DBCheck (DBName NVARCHAR(200));
SET @CheckSQL = '
INSERT INTO #DBCheck (DBName)
SELECT name FROM OPENQUERY(' + QUOTENAME(@LS) + ', ''SELECT name FROM master.sys.databases'')
WHERE name = ''' + @DBName + ''';
';
EXEC(@CheckSQL);
IF NOT EXISTS (SELECT 1 FROM #DBCheck)
BEGIN
DECLARE @CreateDbSQL NVARCHAR(MAX);
SET @CreateDbSQL = N'CREATE DATABASE ' + QUOTENAME(@DBName);
SET @CreateDbSQL = N'EXEC(''' + REPLACE(@CreateDbSQL, '''', '''''') + N''') AT ' + QUOTENAME(@LS);
EXEC sp_executesql @CreateDbSQL;
PRINT 'Remote DB created: ' + @DBName;
END
ELSE PRINT 'Remote DB exists: ' + @DBName;
SELECT @RowID = MIN(ArchiveID), @MaxID = MAX(ArchiveID)
FROM ADT_ArchiveData
WHERE ArchiveType IN (1, 2) AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
WHILE @RowID IS NOT NULL AND @RowID <= @MaxID
BEGIN
SELECT
@TableName = NULL, @RefTable = '',
@PrimaryKey = NULL, @DateColumn = NULL,
@ArchiveUpTo = NULL, @ArchiveType = NULL;
SELECT
@ColumnList = '', @RefColumnList = '',
@ColListRaw = '', @RefColListRaw = '',
@SrcCnt = 0, @ArcCnt = 0,
@RefSrcCnt = 0, @RefArcCnt = 0,
@RowsArchived = 0, @RowsDeleted = 0,
@SkipTable = 0, @Cnt = 1;
SET @BatchId = NEWID();
SELECT
@TableName = TableName,
@RefTable = ISNULL(RefTables, ''),
@PrimaryKey = ReferenceID,
@DateColumn = CompareField,
@ArchiveUpTo = ArchiveUpTo,
@ArchiveType = ArchiveType
FROM ADT_ArchiveData
WHERE ArchiveID = @RowID AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
IF @TableName IS NULL GOTO NextRowMode3;
SET @ArchiveTable = @TableName + '_Archive';
PRINT CHAR(10) + '-- Mode3 | Table: ' + @TableName + ' | Batch: ' + CAST(@BatchId AS NVARCHAR(50));
INSERT INTO dbo.ArchiveLog (BatchGUID, TableName, ArchiveType, Mode, Status)
VALUES (@BatchId, @TableName, @ArchiveType, 3, 'Running');
BEGIN TRY
-- STEP 1: Capture PKs into local #ArchivePKs
TRUNCATE TABLE #ArchivePKs;
SET @SQL = '
INSERT INTO #ArchivePKs (PK_Value)
SELECT CAST(' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@DateColumn) + ' <= @ArchiveUpTo;
';
EXEC sp_executesql @SQL, N'@ArchiveUpTo DATETIME', @ArchiveUpTo;
SET @SrcCnt = @@ROWCOUNT;
PRINT ' Rows to archive: ' + CAST(@SrcCnt AS VARCHAR(20));
IF @SrcCnt = 0
BEGIN
UPDATE dbo.ArchiveLog SET Status='Completed', RowsMarked=0, RowsArchived=0, RowsDeleted=0, CompletedOn=GETDATE() WHERE BatchGUID=@BatchId;
PRINT ' Nothing to archive.';
GOTO NextRowMode3;
END
-- STEP 2: Build column lists
SET @ColumnList = '';
SELECT @ColumnList = @ColumnList +
QUOTENAME(c.name) + ' ' +
CASE
WHEN t.name IN ('varchar','char','nvarchar','nchar')
THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '(' + CAST(c.precision AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE t.name
END + ' ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TableName)
ORDER BY c.column_id;
IF LEN(@ColumnList) > 0 SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1);
SET @ColListRaw = '';
SELECT @ColListRaw = @ColListRaw + c.name + ','
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@TableName)
ORDER BY c.column_id;
IF LEN(@ColListRaw) > 0 SET @ColListRaw = LEFT(@ColListRaw, LEN(@ColListRaw) - 1);
-- STEP 3: Create main archive table on REMOTE
SET @RemoteSQL = '
IF OBJECT_ID(''' + @ArchiveTable + ''',''U'') IS NULL
CREATE TABLE ' + QUOTENAME(@ArchiveTable) + '(' + @ColumnList + ');
';
SET @SQL = 'EXEC(''USE [' + @DBName + ']; ' + REPLACE(@RemoteSQL, '''', '''''') + ''') AT ' + QUOTENAME(@LS);
EXEC(@SQL);
PRINT ' Archive table ready: ' + @ArchiveTable;
-- STEP 4: Batch INSERT to REMOTE via OPENQUERY
IF @ArchiveType = 1
BEGIN
SET @SQL = '
DECLARE @Cnt INT = 1;
WHILE @Cnt > 0
BEGIN
INSERT OPENQUERY(' + QUOTENAME(@LS) + ',
''SELECT ' + @ColListRaw + ' FROM [' + @DBName + '].[dbo].[' + @ArchiveTable + '] WHERE 1=0'')
SELECT TOP (' + CAST(@BatchSize AS VARCHAR(10)) + ') ' + @ColListRaw + '
FROM ' + QUOTENAME(@TableName) + ' S
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(S.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
WHERE NOT EXISTS (
SELECT 1
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
WHERE A.' + QUOTENAME(@PrimaryKey) + ' = S.' + QUOTENAME(@PrimaryKey) + '
)
ORDER BY S.' + QUOTENAME(@PrimaryKey) + ';
SET @Cnt = @@ROWCOUNT;
END;
';
EXEC sp_executesql @SQL;
-- STEP 5: Count validation (4-part naming, runs locally)
SET @CheckSQL = '
SELECT @ArcCnt = COUNT(*)
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @CheckSQL, N'@ArcCnt INT OUTPUT', @ArcCnt OUTPUT;
IF @ArcCnt <> @SrcCnt
BEGIN
PRINT ' COUNT MISMATCH: Src=' + CAST(@SrcCnt AS VARCHAR(20)) + ' Arc=' + CAST(@ArcCnt AS VARCHAR(20));
-- [FIX-3] Rollback via 4-part naming locally (NOT EXEC AT)
-- 4-part naming runs on local engine → #ArchivePKs visible
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
UPDATE dbo.ArchiveLog
SET Status='Failed',
ErrorMsg='Count mismatch: Src='+CAST(@SrcCnt AS VARCHAR(20))+' Arc='+CAST(@ArcCnt AS VARCHAR(20)),
CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
SET @SkipTable = 1;
END
ELSE
BEGIN
SET @RowsArchived = @SrcCnt;
PRINT ' Main archived: ' + CAST(@ArcCnt AS VARCHAR(20)) + ' rows';
END
END -- ArchiveType=1 main
-- STEP 6: Reference tables on REMOTE
IF @SkipTable = 0 AND @RefTable <> ''
BEGIN
PRINT ' Processing ref tables: ' + @RefTable;
DECLARE RefCursorM3 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value)) FROM dbo.fnGEN_Split(@RefTable, ',') WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefCursorM3;
FETCH NEXT FROM RefCursorM3 INTO @Ref;
WHILE @@FETCH_STATUS = 0 AND @SkipTable = 0
BEGIN
SET @RefArchiveTable = @Ref + '_Archive';
SET @RefColumnList = '';
SET @RefColListRaw = '';
SET @RefSrcCnt = 0;
SET @RefArcCnt = 0;
SELECT @RefColumnList = @RefColumnList +
QUOTENAME(c.name) + ' ' +
CASE
WHEN t.name IN ('varchar','char','nvarchar','nchar')
THEN t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN t.name IN ('decimal','numeric')
THEN t.name + '(' + CAST(c.precision AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE t.name
END + ' ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@Ref)
ORDER BY c.column_id;
IF LEN(@RefColumnList) > 0 SET @RefColumnList = LEFT(@RefColumnList, LEN(@RefColumnList) - 1);
SELECT @RefColListRaw = @RefColListRaw + c.name + ','
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@Ref)
ORDER BY c.column_id;
IF LEN(@RefColListRaw) > 0 SET @RefColListRaw = LEFT(@RefColListRaw, LEN(@RefColListRaw) - 1);
-- Create ref archive table on REMOTE
SET @RemoteSQL = '
IF OBJECT_ID(''' + @RefArchiveTable + ''',''U'') IS NULL
CREATE TABLE ' + QUOTENAME(@RefArchiveTable) + '(' + @RefColumnList + ');
';
SET @SQL = 'EXEC(''USE [' + @DBName + ']; ' + REPLACE(@RemoteSQL, '''', '''''') + ''') AT ' + QUOTENAME(@LS);
EXEC(@SQL);
PRINT ' Ref archive table ready: ' + @RefArchiveTable;
IF @ArchiveType = 1
BEGIN
-- Batch INSERT ref to REMOTE via OPENQUERY
SET @SQL = '
DECLARE @RCnt INT = 1;
WHILE @RCnt > 0
BEGIN
INSERT OPENQUERY(' + QUOTENAME(@LS) + ',
''SELECT ' + @RefColListRaw + ' FROM [' + @DBName + '].[dbo].[' + @RefArchiveTable + '] WHERE 1=0'')
SELECT TOP (' + CAST(@BatchSize AS VARCHAR(10)) + ') ' + @RefColListRaw + '
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450))
WHERE NOT EXISTS (
SELECT 1
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
WHERE A.' + QUOTENAME(@PrimaryKey) + ' = R.' + QUOTENAME(@PrimaryKey) + '
)
ORDER BY R.' + QUOTENAME(@PrimaryKey) + ';
SET @RCnt = @@ROWCOUNT;
END;
';
EXEC sp_executesql @SQL;
-- Ref count validation (4-part naming, runs locally)
SET @SQL = '
SELECT @RefSrcCnt = COUNT(*)
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL, N'@RefSrcCnt INT OUTPUT', @RefSrcCnt OUTPUT;
SET @CheckSQL = '
SELECT @RefArcCnt = COUNT(*)
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @CheckSQL, N'@RefArcCnt INT OUTPUT', @RefArcCnt OUTPUT;
IF @RefSrcCnt <> @RefArcCnt
BEGIN
PRINT ' REF MISMATCH [' + @Ref + ']: Src=' + CAST(@RefSrcCnt AS VARCHAR(20)) + ' Arc=' + CAST(@RefArcCnt AS VARCHAR(20));
SET @SkipTable = 1;
UPDATE dbo.ArchiveLog
SET Status='Failed',
ErrorMsg='Ref mismatch ['+@Ref+']: Src='+CAST(@RefSrcCnt AS VARCHAR(20))+' Arc='+CAST(@RefArcCnt AS VARCHAR(20)),
CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
END
ELSE PRINT ' Ref archived: ' + @Ref + ' (' + CAST(@RefArcCnt AS VARCHAR(20)) + ' rows)';
END
FETCH NEXT FROM RefCursorM3 INTO @Ref;
END
CLOSE RefCursorM3; DEALLOCATE RefCursorM3;
-- [FIX-3] Rollback via 4-part naming (NOT EXEC AT)
IF @SkipTable = 1
BEGIN
PRINT ' Mismatch — rolling back remote archive data...';
DECLARE RefRBM3 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value)) FROM dbo.fnGEN_Split(@RefTable, ',') WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefRBM3; FETCH NEXT FROM RefRBM3 INTO @Ref;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RefArchiveTable = @Ref + '_Archive';
-- 4-part naming: local engine → remote table, #ArchivePKs visible
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@RefArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Ref rollback: ' + @Ref;
FETCH NEXT FROM RefRBM3 INTO @Ref;
END
CLOSE RefRBM3; DEALLOCATE RefRBM3;
-- Rollback main archive on REMOTE via 4-part naming
SET @SQL = '
DELETE A
FROM ' + QUOTENAME(@LS) + '.' + QUOTENAME(@DBName) + '.[dbo].' + QUOTENAME(@ArchiveTable) + ' A
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(A.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Main archive rollback: ' + @TableName;
END
END -- ref tables
-- STEP 7: Delete from SOURCE (local — same as Mode 2)
IF @SkipTable = 0 AND @ArchiveType IN (1, 2)
BEGIN
IF @RefTable <> ''
BEGIN
DECLARE RefDelM3 CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(Value)) FROM dbo.fnGEN_Split(@RefTable, ',') WHERE LTRIM(RTRIM(Value)) <> '';
OPEN RefDelM3; FETCH NEXT FROM RefDelM3 INTO @Ref;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
DELETE R
FROM ' + QUOTENAME(@Ref) + ' R
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(R.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
PRINT ' Ref source deleted: ' + @Ref + ' (' + CAST(@@ROWCOUNT AS VARCHAR(20)) + ' rows)';
FETCH NEXT FROM RefDelM3 INTO @Ref;
END
CLOSE RefDelM3; DEALLOCATE RefDelM3;
END
SET @SQL = '
DELETE S
FROM ' + QUOTENAME(@TableName) + ' S
INNER JOIN #ArchivePKs P
ON P.PK_Value = CAST(S.' + QUOTENAME(@PrimaryKey) + ' AS NVARCHAR(450));
';
EXEC sp_executesql @SQL;
SET @RowsDeleted = @@ROWCOUNT;
PRINT ' Source deleted: ' + CAST(@RowsDeleted AS VARCHAR(20)) + ' rows from ' + @TableName;
UPDATE dbo.ArchiveLog
SET Status='Completed', RowsMarked=@SrcCnt, RowsArchived=@RowsArchived, RowsDeleted=@RowsDeleted, CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId;
PRINT ' DONE: ' + @TableName;
END
END TRY
BEGIN CATCH
IF CURSOR_STATUS('local','RefCursorM3') >= 0 BEGIN CLOSE RefCursorM3; DEALLOCATE RefCursorM3; END
IF CURSOR_STATUS('local','RefRBM3') >= 0 BEGIN CLOSE RefRBM3; DEALLOCATE RefRBM3; END
IF CURSOR_STATUS('local','RefDelM3') >= 0 BEGIN CLOSE RefDelM3; DEALLOCATE RefDelM3; END
UPDATE dbo.ArchiveLog
SET Status='Failed', ErrorMsg=ERROR_MESSAGE(), CompletedOn=GETDATE()
WHERE BatchGUID=@BatchId AND TableName=@TableName;
PRINT ' ERROR Mode3 [' + @TableName + ']: ' + ERROR_MESSAGE();
EXECUTE usp_ErrorDB_AddNew;
END CATCH
NextRowMode3:
SELECT @RowID = MIN(ArchiveID) FROM ADT_ArchiveData
WHERE ArchiveID > @RowID AND ArchiveType IN (1,2) AND (@ArchiveID IS NULL OR ArchiveID = @ArchiveID);
END -- WHILE Mode3
EXEC usp_LinkedServiceManager @Action = 2;
END -- Mode 3
PRINT CHAR(10) + '>> Done. SELECT * FROM dbo.ArchiveLog ORDER BY StartedOn DESC;';
END
GO
/*
Test single: EXEC usp_RunArchiveProcess @ArchiveID = 4;
Test all: EXEC usp_RunArchiveProcess @ArchiveID = 0;
Monitor: SELECT * FROM dbo.ArchiveLog ORDER BY StartedOn DESC;
*/Ye ek dynamic, configurable archiving engine hai jo:
Multiple tables handle karta hai
Reference tables bhi archive karta hai
Batch me data process karta hai
Logs maintain karta hai
Step-by-Step Understanding
1. Auto Logging System (ArchiveLog Table)
IF OBJECT_ID('dbo.ArchiveLog', 'U') IS NULL
CREATE TABLE dbo.ArchiveLog (...)Use Case
Har archive run track hota hai
Failures trace kar sakte ho
Audit ready system
2. Configuration Driven Design
FROM ScheduledTaskSettings
WHERE SchTaskID = 12
Kya ho raha hai?
Server name
Database name
Username
Password (If declare)
Ye sab config table se aa raha hai
Benefit
Hardcoding avoid
Easily environment change (DEV → UAT → PROD)
3. Mode Detection (Smart Logic)
IF @ArcServer = @SrvName AND @DBName = DB_NAME() SET @Mode = 1;
ELSE IF @ArcServer = @SrvName SET @Mode = 2;
ELSE SET @Mode = 3;
3 Modes:
Mode | Meaning |
|---|---|
1 | Same DB (No action) |
2 | Same Server, Different DB |
3 | Different Server (Linked Server) |
📦 4. PK-Based Archiving Strategy
CREATE TABLE #ArchivePKs (PK_Value NVARCHAR(450))
Concept
Pehle Primary Keys identify karo
Phir unhi rows ko archive karo
Why Important?
👉 Direct DELETE dangerous hota hai
👉 PK-based approach = safe + controlled
Tip
NVARCHAR(450 use kiya gaya hai kyunki:
INT
BIGINT
GUID
sab handle ho jaye
5. Batch Processing (Performance Booster)
@BatchSize = 5000
Why Batch?
Large data = memory issue + locks
Example
❌ 1 million rows ek saath → lock, timeout
✅ 5000 batch → smooth processing
6. Dynamic Archive Table Creation
CREATE TABLE [ArchiveTable]Kya ho raha hai?
Source table ka structure copy ho raha hai
Same columns, same datatype
Real Use
👉 No manual table creation required
👉 Fully automated system
7. Data Insert with Duplicate Protection
WHERE NOT EXISTS (...)Explanation
Duplicate rows insert nahi honge
8. Count Validation (MOST IMPORTANT 🔥)
IF @ArcCnt <> @SrcCntKya check ho raha hai?
Source rows count
Archive rows count
Agar mismatch:
Data rollback
Log update
Golden Rule
👉 Never delete data without validation
9. Reference Table Handling
@RefTableExample
Order table
OrderDetails table
Dono ko sync me archive karna padta hai
Concept
👉 Parent-child integrity maintain
10. Rollback Mechanism
DELETE A FROM ArchiveTableKab hota hai?
Count mismatch
Error
Real Insight
👉 Production me rollback hona MUST hai
warna partial data = disaster
11. Safe Deletion
DELETE FROM SourceCondition
Only after successful archive
Rule
👉 Archive first → Validate → Then delete
Common Mistakes (Avoid These)
❌ Direct delete without backup
❌ No logging
❌ No validation
❌ No batch processing
❌ Ignoring reference tables
Real-World Scenario
Suppose:
Attendance data 5 saal ka hai
Daily queries slow ho rahi hain
👉 Solution:
2 saal old data archive karo
Reporting fast ho jayegi
Storage optimize hoga
Developer / DBA Tips
Always test with small data first
Use TRY/CATCH (already used here )
Monitor logs regularly
Schedule via SQL Agent Job
Index archive tables me bhi maintain karo
Summary
Is blog me humne ek enterprise-grade archiving system dekha jo:
Config driven hai
Multi-mode support karta hai
Safe hai (validation + rollback)
Scalable hai (batch processing)
Advanced hai (linked server support)
Ye ek production-ready solution hai jo real companies me use hota hai