Introduction
As databases grow, performance starts degrading. Queries become slow, indexes become heavy, and maintenance becomes difficult.
This is where data archiving plays a very important role.
In this blog, you will learn:
How to design an archive configuration table
How to store archiving rules
How to build a powerful dynamic archiving stored procedure
How to handle same DB, different DB, and different server archiving
Real-world DBA practices and common mistakes
This guide is based on a practical production-level implementation.
Previous Concept Recap (Why Archiving?)
Before diving in, understand the purpose:
Old data is rarely used
But still important for history / audit
So we:
Move it to archive tables
Keep main tables lightweight
👉 Example:
AttendanceDetails→ active dataAttendanceDetails_Archive→ old data
Step 1: Archive Configuration Table
We create a central configuration table.
Table: ADT_ArchiveData
CREATE TABLE [dbo].[ADT_ArchiveData](
[ArchiveID] [int] NOT NULL PRIMARY KEY,
[TableName] [varchar](100) NOT NULL,
[DisplayName] [varchar](100) NOT NULL,
[ArchiveUpTo] [smalldatetime] NULL,
[RefTables] [varchar](1000) NOT NULL, --Child Table (FK linking tables) comma seperate
[ArchiveType] [tinyint] NOT NULL, --1=> Archive+Delete, 2=> Delete Only
[ReferenceID] [varchar](50) NOT NULL,
[CompareField] [varchar](50) NOT NULL --Datefield
)Understanding Important Columns (Simple Explanation)
🔹 TableName
Main table to archive
👉 Example: TimeEntry
🔹 ArchiveUpTo
If ArchiveUpTo = '2026-01-01', then:
All records with date less than 01 Jan 2026 (
< '2026-01-01') will be archivedData on or after this date will remain in the main table
🔹 ReferenceID
Primary key column
👉 Used to identify rows uniquely
🔹 CompareField
Date column used for filtering
👉 Example: CreateDate
🔹 RefTables
Child tables (related tables)
👉 Example:
ADT_Tasks, ADT_TransData🔹 ArchiveType
1= Move data + delete from source2= Only delete
Step 2: Archive Execution Procedure
The core logic is inside a stored procedure:
👉 usp_RunArchiveProcess
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 ADT_ArchiveLog
-- =========================================================================
IF OBJECT_ID('dbo.ADT_ArchiveLog', 'U') IS NULL
BEGIN
CREATE TABLE dbo.ADT_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 'ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_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.ADT_ArchiveLog ORDER BY StartedOn DESC;
*/Key Concepts in the Procedure
1. Auto Log Table Creation
ADT_ArchiveLogTracks:
Rows archived
Rows deleted
Errors
Execution time
👉 Best Practice: Always maintain logs in production systems.
2. Dynamic Mode Detection
System automatically detects:
Mode | Description |
|---|---|
1 | Same DB (skip) |
2 | Same Server, Different DB |
3 | Different Server (Linked Server) |
👉 This makes the system fully flexible
3. PK Capture Strategy
INSERT INTO #ArchivePKs
SELECT CAST(PrimaryKey AS NVARCHAR(450))
👉 Why?
Works with all data types (INT, GUID, VARCHAR)
Simplifies joins
4. Dynamic Table Creation
Archive tables are created automatically:
CREATE TABLE TableName_Archive (...)👉 No manual work needed
5. Data Archiving Logic
INSERT INTO ArchiveTable
SELECT * FROM SourceTable
WHERE Date <= @ArchiveUpTo
With:
Duplicate check (
NOT EXISTS)Batch processing (for performance)
6. Data Validation (Very Important)
IF @SrcCnt <> @ArcCnt👉 If counts don’t match:
Rollback archive
Mark as failed
💡 Golden Rule:
Never delete source data without validation.
7. Reference Table Handling
Uses cursor to process child tables
Ensures relational data consistency
👉 Example:
If parent archived → child must also be archived
8. Rollback Strategy
If anything fails:
Delete archive data
Keep source intact
👉 Production-safe design
9. Delete from Source
After successful archive:
DELETE FROM SourceTable👉 Keeps main DB fast and clean
Real-World DBA Tips
✅ Tip 1: Always Archive in Batches
TOP (5000)✔ Prevents:
Blocking
Log file explosion
✅ Tip 2: Always Use Logging Table
Never run archive blindly.
✅ Tip 3: Use Proper Index
Ensure:
PK indexed
Date column indexed
✅ Tip 4: Run in Off-Peak Hours
Schedule via SQL Agent job.
Common Mistakes
❌ Direct delete without archive
❌ No validation after insert
❌ Ignoring child tables
❌ Running huge delete in one go
❌ Not handling failures
Real-World Example Scenario
Company has:
5 years of attendance data
Daily inserts happening
Solution:
Keep last 6 months in main table
Archive rest monthly
Result:
Query performance improved
Storage optimized
Backup size reduced
Summary
This system provides:
Fully dynamic archiving
Multi-table support
Safe rollback mechanism
Cross-server capability
Logging and monitoring
👉 In simple words:
"Move old data safely, keep system fast."
Final Thoughts
If you are working as:
SQL Developer
DBA
Data Engineer
Then this kind of archiving system is must-have in production.