Create the Archive Filegroup and File

Placing archive data on a dedicated filegroup separates it physically from production data. This gives you independent backup, restore, and storage management for archived data.

-- Add a dedicated archive filegroup to the database
ALTER DATABASE YourDatabase
ADD FILEGROUP ArchiveFG;

-- Add a data file to the filegroup
-- Update the FILENAME path to match your environment
ALTER DATABASE YourDatabase
ADD FILE
(
    NAME       = N'ArchiveFile',
    FILENAME   = N'D:\SQLData\ArchiveFile.ndf',
    SIZE       = 500MB,        -- Size based on expected archive volume
    MAXSIZE    = UNLIMITED,
    FILEGROWTH = 256MB
) TO FILEGROUP ArchiveFG;

-- Verify the filegroup was created
SELECT name, type_desc, is_default, is_read_only
FROM sys.filegroups
ORDER BY name;

Create the Archive Table

The archive table must live on the ArchiveFG filegroup. Match the schema of your source table exactly. Add an ArchivedDate column to record when each row was archived — useful for auditing and troubleshooting.

-- Create the archive table on the archive filegroup
-- Replace columns to match your actual table structure
CREATE TABLE dbo.YourTableArchive
(
    -- Match all columns from YourTable exactly
    ID            INT           NOT NULL,
    DateColumn    DATETIME      NOT NULL,
    Description   NVARCHAR(500) NULL,
    Amount        DECIMAL(18,2) NULL,
    CreatedBy     NVARCHAR(100) NULL,
    -- Add all your source table columns here

    -- Additional archive metadata column
    ArchivedDate  DATETIME      NOT NULL
        CONSTRAINT DF_YourTableArchive_ArchivedDate DEFAULT (GETDATE()),

    CONSTRAINT PK_YourTableArchive PRIMARY KEY CLUSTERED (ID)
)
ON ArchiveFG;  -- Placed on the archive filegroup

-- Add any nonclustered indexes matching the source table
-- that are needed for reporting queries against archive data
CREATE NONCLUSTERED INDEX IX_YourTableArchive_DateColumn
ON dbo.YourTableArchive (DateColumn)
ON ArchiveFG;

Create the Archive Log Table

The log table records every archive run — when it ran, how many rows moved, whether it succeeded, and any error messages. This is essential for monitoring and troubleshooting.

-- Archive process audit log
CREATE TABLE dbo.ArchiveProcessLog
(
    LogID            INT           IDENTITY(1,1) NOT NULL,
    RunDate          DATETIME      NOT NULL DEFAULT (GETDATE()),
    ArchiveThreshold DATETIME      NOT NULL,
    RowsArchived     INT           NOT NULL DEFAULT (0),
    RowsDeleted      INT           NOT NULL DEFAULT (0),
    BatchCount       INT           NOT NULL DEFAULT (0),
    DurationSeconds  INT           NULL,
    Status           VARCHAR(20)   NOT NULL DEFAULT ('Running'),
    ErrorMessage     NVARCHAR(MAX) NULL,
    CONSTRAINT PK_ArchiveProcessLog PRIMARY KEY CLUSTERED (LogID)
);

Create the Archive Stored Procedure

This is the core of the archive process. Key design decisions in this procedure:

• Batched processing — moves rows in small batches to control log growth and reduce blocking
• Transaction per batch — each batch commits independently so a failure does not roll back hours of work
• TRY/CATCH — errors are caught, logged, and reported rather than failing silently
• Dry-run mode — run with @ExecuteArchive = 0 to preview what would be archived
• Configurable threshold — the archive cutoff date is a parameter, not hardcoded

CREATE PROCEDURE dbo.MoveDataToArchive
(
     @MonthsToRetain   INT  = 6      -- Keep this many months in main table
    ,@BatchSize        INT  = 10000  -- Rows per batch — adjust for your environment
    ,@ExecuteArchive   BIT  = 1      -- 0 = dry run (count only), 1 = execute
)
AS
/*
    MoveDataToArchive
    -----------------
    Moves data older than @MonthsToRetain months from YourTable
    to YourTableArchive in batches.

    Parameters:
        @MonthsToRetain  - Number of months of data to keep in the main table (default: 6)
        @BatchSize       - Rows moved per iteration (default: 10,000)
        @ExecuteArchive  - 0 = dry run shows row count only, 1 = execute the archive

    Test (dry run — no data moved):
        EXEC dbo.MoveDataToArchive @MonthsToRetain = 6, @ExecuteArchive = 0

    Test (execute with small batch for validation):
        EXEC dbo.MoveDataToArchive @MonthsToRetain = 6, @BatchSize = 1000, @ExecuteArchive = 1
*/
SET NOCOUNT ON;

DECLARE
     @ArchiveThreshold DATETIME
    ,@RowsThisBatch    INT
    ,@TotalArchived    INT = 0
    ,@TotalDeleted     INT = 0
    ,@BatchCount       INT = 0
    ,@LogID            INT
    ,@StartTime        DATETIME = GETDATE()
    ,@ErrorMessage     NVARCHAR(MAX);

-- Calculate the archive threshold date
SET @ArchiveThreshold = DATEADD(MONTH, -@MonthsToRetain, GETDATE());

-- -------------------------------------------------------
-- Dry run: show what would be archived without moving data
-- -------------------------------------------------------
IF @ExecuteArchive = 0
BEGIN
    SELECT
        'DRY RUN — No data moved'                     AS Mode,
        @ArchiveThreshold                             AS ArchiveThreshold,
        COUNT(*)                                      AS RowsToArchive,
        MIN(DateColumn)                               AS OldestRow,
        MAX(DateColumn)                               AS NewestRowToArchive
    FROM dbo.YourTable
    WHERE DateColumn < @ArchiveThreshold;
    RETURN;
END

-- -------------------------------------------------------
-- Log the start of this archive run
-- -------------------------------------------------------
INSERT INTO dbo.ArchiveProcessLog (RunDate, ArchiveThreshold, Status)
VALUES (GETDATE(), @ArchiveThreshold, 'Running');

SET @LogID = SCOPE_IDENTITY();

-- -------------------------------------------------------
-- Batched archive: INSERT then DELETE in small transactions
-- -------------------------------------------------------
BEGIN TRY

    WHILE 1 = 1
    BEGIN

        -- Insert one batch into the archive table
        INSERT INTO dbo.YourTableArchive
        (
            ID, DateColumn, Description, Amount, CreatedBy,
            -- list all columns from YourTable
            ArchivedDate
        )
        SELECT TOP (@BatchSize)
            ID, DateColumn, Description, Amount, CreatedBy,
            -- list all columns from YourTable
            GETDATE() AS ArchivedDate
        FROM dbo.YourTable
        WHERE DateColumn < @ArchiveThreshold
        ORDER BY DateColumn ASC;  -- Archive oldest first

        SET @RowsThisBatch = @@ROWCOUNT;

        -- No rows inserted — all qualifying data has been archived
        IF @RowsThisBatch = 0 BREAK;

        SET @TotalArchived += @RowsThisBatch;
        SET @BatchCount    += 1;

        -- Delete the same rows from the source table
        -- Match on the same criteria to avoid deleting wrong rows
        DELETE TOP (@BatchSize) FROM dbo.YourTable
        WHERE DateColumn < @ArchiveThreshold
        AND ID IN
        (
            SELECT TOP (@BatchSize) ID
            FROM dbo.YourTable
            WHERE DateColumn < @ArchiveThreshold
            ORDER BY DateColumn ASC
        );

        SET @TotalDeleted += @@ROWCOUNT;

        -- Brief pause between batches to reduce I/O pressure
        -- Remove or adjust based on your environment
        WAITFOR DELAY '00:00:01';

    END; -- end of WHILE loop

    -- Update statistics on both tables after archiving
    UPDATE STATISTICS dbo.YourTable;
    UPDATE STATISTICS dbo.YourTableArchive;

    -- Log successful completion
    UPDATE dbo.ArchiveProcessLog
    SET
        RowsArchived    = @TotalArchived,
        RowsDeleted     = @TotalDeleted,
        BatchCount      = @BatchCount,
        DurationSeconds = DATEDIFF(SECOND, @StartTime, GETDATE()),
        Status          = 'Completed'
    WHERE LogID = @LogID;

    -- Return summary
    SELECT
        @ArchiveThreshold  AS ArchiveThreshold,
        @TotalArchived     AS TotalRowsArchived,
        @TotalDeleted      AS TotalRowsDeleted,
        @BatchCount        AS BatchesProcessed,
        DATEDIFF(SECOND, @StartTime, GETDATE()) AS DurationSeconds;

END TRY
BEGIN CATCH

    SET @ErrorMessage = ERROR_MESSAGE();

    -- Log the failure
    UPDATE dbo.ArchiveProcessLog
    SET
        RowsArchived    = @TotalArchived,
        RowsDeleted     = @TotalDeleted,
        BatchCount      = @BatchCount,
        DurationSeconds = DATEDIFF(SECOND, @StartTime, GETDATE()),
        Status          = 'Failed',
        ErrorMessage    = @ErrorMessage
    WHERE LogID = @LogID;

    -- Re-raise the error so the Agent job captures it
    RAISERROR(@ErrorMessage, 16, 1);

END CATCH;
GO

Test the Stored Procedure

Always test before scheduling. Use the dry-run mode first to confirm what data qualifies, then run with a small batch size to validate the archive and delete logic before full execution.

-- Step 1: Dry run — confirms row count and date range, moves nothing
EXEC dbo.MoveDataToArchive
    @MonthsToRetain = 6,
    @ExecuteArchive = 0;

-- Step 2: Execute with small batch to validate first 1,000 rows
EXEC dbo.MoveDataToArchive
    @MonthsToRetain = 6,
    @BatchSize      = 1000,
    @ExecuteArchive = 1;

-- Step 3: Confirm rows moved correctly
SELECT COUNT(*) AS MainTableCount  FROM dbo.YourTable        WHERE DateColumn < DATEADD(MONTH, -6, GETDATE());
SELECT COUNT(*) AS ArchiveCount    FROM dbo.YourTableArchive WHERE DateColumn < DATEADD(MONTH, -6, GETDATE());

-- Step 4: Check the log
SELECT * FROM dbo.ArchiveProcessLog ORDER BY LogID DESC;

Schedule with SQL Server Agent

Once validated, create a SQL Server Agent job to run the archive procedure automatically on a schedule.

USE msdb;
GO

-- Create the Agent job
EXEC sp_add_job
    @job_name = N'DBA - Archive YourTable Data';

-- Add the job step
EXEC sp_add_jobstep
    @job_name      = N'DBA - Archive YourTable Data',
    @step_name     = N'Run Archive Procedure',
    @database_name = N'YourDatabase',
    @command       = N'EXEC dbo.MoveDataToArchive
                           @MonthsToRetain = 6,
                           @BatchSize      = 10000,
                           @ExecuteArchive = 1;',
    @on_success_action = 1,  -- Quit with success
    @on_fail_action    = 2;  -- Quit with failure (alerts DBA via Agent notification)

-- Schedule: run weekly on Sunday at 2:00 AM
EXEC sp_add_schedule
    @schedule_name     = N'Weekly Sunday 2AM',
    @freq_type         = 8,      -- Weekly
    @freq_interval     = 1,      -- Sunday
    @freq_recurrence_factor = 1,
    @active_start_time = 020000; -- 02:00:00

EXEC sp_attach_schedule
    @job_name      = N'DBA - Archive YourTable Data',
    @schedule_name = N'Weekly Sunday 2AM';

EXEC sp_add_jobserver
    @job_name = N'DBA - Archive YourTable Data';
GO