How to do automatic data archiving in SQL Server?

Question

I have table for which every day I want to do automatic archiving. So to be clear every day I want to take information generated during that day and move it into another partition (of same table) not in another archive table. That's because I want old data to be accessible with same query as new ones.

I'm using SQL Server 2005, I've read http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx article but couldn't find out how can I write partitioning function to satisfy my needs.

So the solution I hope exists should be one time configuration which won't need any further interference. Do you have any suggestions?

Answer 1

You can easily do this with partioned tables; example script below -- it creates a temporary db TestDB; if you dont want to use it change the database to something else. It cleans itself up at the end IF you run the script as is it Creates the database; adds a partitioning funcition based on a Bit. Creates a table TestTable; applied with the partitioning function Inerts 3 "Live" rows into the table Shows that 3 rows are all in one of the partition tables by selecting details from sys.partitions THen updates one of the records to make it archived Reselects information from sys.partitions to show that the record has moved to the second schema.

All you would need to do is setup a process to archive the records.

USE master;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'c:\sqldata\TestDB_Part1.mdf',
SIZE=3,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'c:\sqldata\TestDB_Part2.ndf',
SIZE =3,
MAXSIZE=100,
FILEGROWTH=1 );
GO



USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (Bit)
AS RANGE right FOR
VALUES (1);
GO

CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO


CREATE TABLE TestTable
(Archived Bit NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (Archived);
GO


INSERT INTO TestTable (Archived, Date) 
VALUES (0,'2010-01-01');
INSERT INTO TestTable (Archived, Date) 
VALUES (0,'2010-02-01');
INSERT INTO TestTable (Archived, Date) 
VALUES (0,'2010-03-01');
GO

SELECT * FROM TestTable;


SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

update TestTable 
set Archived = 1 where Date = '2010-03-01'

SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';


use master
go
drop database testdb