Post reply

Data Archiving Methods

  • November 23, 2010 at 7:06 am

    #231914

    Hi All

    I have got a database which is of 1.3 TB, there is a main table which holds more than a billion rows, Now I face a mammoth task of archiving this table into a different database on a monthly basics, I cant do a traditional archiving process since the time it takes to delete this records.

    I thought to go for partitioning, but then came the problem,

    Once the partition has been created i can move the data into a separate table on the same file group as the moving partition lies. but from here how do i move the table into the new database and on that database how do i insert this data into the archive table which is on a new database .

    All your help is much appreciated.

  • November 23, 2010 at 7:46 am

    #1254468

    Hi CrazyMan,

    You can also partition a sql table.

    You can place each partition to different files.

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • November 23, 2010 at 8:04 am

    #1254478

    CrazyMan (11/23/2010)

    Hi All

    I have got a database which is of 1.3 TB, there is a main table which holds more than a billion rows, Now I face a mammoth task of archiving this table into a different database on a monthly basics, I cant do a traditional archiving process since the time it takes to delete this records.

    I thought to go for partitioning, but then came the problem,

    Once the partition has been created i can move the data into a separate table on the same file group as the moving partition lies. but from here how do i move the table into the new database and on that database how do i insert this data into the archive table which is on a new database .

    All your help is much appreciated.

    you can create a small SSIS package to archive the data into different database. this can be scheduled to run on a weekly or monthly basis to archive the data from the main table.

    Regards,
    Pravasis

  • November 23, 2010 at 8:15 am

    #1254488

    Thanks Eralper

    How can I achieve this one, when i tried to create a table on a different partition its not allowing me to create one.

  • November 23, 2010 at 11:37 pm

    #1254899

    Hello CrazyMan,

    In sql article you can find How to Create Partition Function on SQL Server 2008 as well as How to Create Partition Scheme

    Using these partition function and scheme, the following Create Table syntax will create a partitioned table in SQL2008

    CREATE TABLE SQLPartitionTableSample

    (

    LastName nvarchar(50),

    FirstName nvarchar(50)

    )

    ON ps_PartitionScheme(LastName)

    I hope that helps,

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply