Archiving MSSQL Database and Retrieving the archived data for reporting I am still an amateur when it comes to MS SQL Server.

I have been tasked with Archiving our Production Database which is growing daily.  Mainly I am looking at the transactions tables, simply because they are the ones that generate the most data (average of a million records per day). 

I understand that the tables from which they pull data from might need to be archived as well for referential integrity purposes. 

I also need a way to be able to query this archived data whenever needed as quickly as possible if there's a report needed. Any advice?
It sounds like whatever application is feeding this database is dumping tons of records in there if as you say there are 1 million transactions a day.  See if whatever is feeding this database has a retention feature.  If you have 1 million + records a day you are going to run out of space quickly.

There are a lot of factors involved in this type of process.  Size of the database, number of transactions, network speeds, storage space, etc...

If I were in your shoes at this moment I would restore a copy of your backup either to another instance or to the same instance if you have room.  I would truncate out all data that is not needed.  I would then create a SQL agent job that will insert the data into the new database tables with the GetDate() function and whatever offset time is needed and do it in a batch function of maybe 1000 per batch.

Here is a sample of one that I use.  It can be an insert or delete batch:

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r &gt; 0
    BEGIN
        BEGIN TRANSACTION;
        --This could be an INSERT or DELETE etc.
        DELETE TOP (1000)
        FROM dbo.application_log
        WHERE(DATEADD(MM, -6, GETDATE()) &gt; date_created);
        SET @r = @@ROWCOUNT;
        COMMIT TRANSACTION;
    END;


After that is completed, then run a delete batch to remove the data from the primary database, which will purge the old data.

Keep in mind this is a very simplistic model of a data transfer and purge.  There are many ways to accomplish this, but require a greater knowledge of SQL and would take much longer to explain than we have time for here.

Hope that helps.