Partitioning Table - Sliding window

I have a database of 3 terabytes in production with 4 big partitioned tables of sizes between 500GB to 900GB. I was planning to move one year old data into some temporary database and backup that one to tapes and delete that old data from actual database.

here are the three easy steps to do it,

Step 1: Check for the partition from partition function,

select $partition.MonthlyPFN('2005-May-01')

It will give information, in which partition, data is stored for May 2005, My tables are partitioned on monthly basis.

It has returned, FG4, as initially I created partitions for 5 years in March 2005, to keep data till 2010 in partitions from FG1 to FG60. Monthly Partitioned tables.

Step 2: Create a Table exactly same as of the table, from where you want to delete or move data, in same file group,

CREATE TABLE [ods].[BLA_BLA_MAY_2005](
[ID] [bigint] NOT NULL,
[FILENAME] [varchar](100) NULL,
[DATELOADED] [datetime] NULL,
) ON [FG4]

Step 3: Use Alter table switch statement to move data to temporary table, which you can drop later,

ALTER TABLE SCHEMA.SOURCE_TABLE SWITCH partition SOURCE_PARTITION_NUMBER TO SCHEMA.TARGET_TABLE partition TARGET_PARTITION_NUMBER

e.g., ALTER TABLE ODS.BLA_BLA SWITCH partition 4 TO ODS.BLA_BLA_MAY_2005

for more help on alter table switch, visit: http://msdn2.microsoft.com/en-us/library/ms190273.aspx