Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Question Unanswered: Archive off data

    Hi ,

    Project assigned to me called ''Archive off old data''.As SQL DBA the best way to archive large table is data Partitioning. But one table does not enough in terms of size to do partition and plus table has 32 other tables,view,sps dependencies.The data from that table needs to archive off every week to different database on different server. My question are

    1) what is the best way to archive data ?
    2) what are steps I need to follow ? Do I need to remove dependencies first and then take out data?.

    Can you please send me link or steps to accomplish this project ?
    Please let me know if i am not clear above.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would use bcp with queryout


    Can you post the DDL of the table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2012
    Posts
    5
    Thanks for reply Brett..

    DDL code :

    USE [DB]
    GO

    /****** Object: Table [dbo].[Table_name] Script Date: 3/26/2012 1:05:08 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[Table_name](
    [UserID] [int] NOT NULL,
    [AwardID] [int] NOT NULL,
    [P_ID] [int] NOT NULL,
    [Due_month] [int] NOT NULL,
    [Q_ID] [int] NOT NULL,
    [A_ID] [int] NOT NULL,
    [Ans] [varchar](1000) NULL,
    [Enabled] [bit] NOT NULL,
    [Required] [bit] NOT NULL,
    [IsDefault] [bit] NOT NULL,
    [DateLastUpdated] [datetime] NOT NULL,
    [LockOverride] [bit] NOT NULL,
    [OldMonthDue] [int] NULL,
    CONSTRAINT [PK_APP_Q_A] PRIMARY KEY CLUSTERED
    (
    [UserID] ASC,
    [AwardID] ASC,
    [P_ID] ASC,
    [Due_month] ASC,
    [Q_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[Table_name] ADD CONSTRAINT [DF_Table_name_Enabled] DEFAULT ((0)) FOR [Enabled]
    GO

    ALTER TABLE [dbo].[Table_name] ADD CONSTRAINT [DF_Table_name_Required] DEFAULT ((0)) FOR [Required]
    GO

    ALTER TABLE [dbo].[Table_name] ADD CONSTRAINT [DF_Table_name_IsDefault] DEFAULT ((0)) FOR [IsDefault]
    GO

    ALTER TABLE [dbo].[Table_name] ADD CONSTRAINT [DF_Table_name_DateLastUpdated] DEFAULT (getdate()) FOR [DateLastUpdated]
    GO

    ALTER TABLE [dbo].[Table_name] ADD CONSTRAINT [DF__Applicati__LockO__50B67AC9] DEFAULT ((0)) FOR [LockOverride]
    GO

    ALTER TABLE [dbo].[Table_name] WITH NOCHECK ADD CONSTRAINT [FK_Table_name_AnswerState] FOREIGN KEY([A_ID])
    REFERENCES [dbo].[ANSWERSTATE] ([ID])
    GO

    ALTER TABLE [dbo].[Table_name] CHECK CONSTRAINT [FK_Table_name_AnswerState]
    GO

    ALTER TABLE [dbo].[Table_name] WITH NOCHECK ADD CONSTRAINT [FK_Table_name_AwardYearQuestions] FOREIGN KEY([Q_ID], [AwardID])
    REFERENCES [dbo].[AwardYearQuestions] ([Q_ID], [AwardID])
    GO

    ALTER TABLE [dbo].[Table_name] CHECK CONSTRAINT [FK_Table_name_AwardYearQuestions]
    GO

    ALTER TABLE [dbo].[Table_name] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationQuestionAnswers_Application1] FOREIGN KEY([UserID], [AwardID], [P_ID], [MonthDue])
    REFERENCES [dbo].[Application] ([UserID], [AwardID], [P_ID], [Duemonth])
    ON DELETE CASCADE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •