How to do automatic data archiving in SQL Server?

Question

I have a table for which I want to do automatic archiving every day. Therefore, to be clear every day, I want to take the information created during this day and move it to another section (of the same table), and not to another archive table. This is because I want the old data to be available with the same query as the new one.

I am using SQL Server 2005, I read the article http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx , but could not find out how I can write partitioning to suit my needs .

Thus, a solution that I hope exists should be a one-time configuration that does not need any additional interference. Do you have any suggestions?

Answer 1

; script - db TestDB; , - . script, ; . TestTable; 3 "Live" , 3 , sys.partitions THen , sys.partitions, , .

, , .

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