Perfect way for transfering data

• DuruG

  SSCrazy

  Points: 2089

  More actions

  September 25, 2004 at 9:51 pm

  #65687

  Hi everybody,

  I will describe in few words the situation I need help with:

  We have a production server, with SQL2000. The application that is running on that server is inserting data into the MS SQL Database. Since there are more than 500 000 records per day inserted in to the DB, the application is deleting data that is older than one month. Shortly, the DB is holding data just for one month.

  Our purpose is to have the other server, a powerful dedicated SQL Server to hold a copy of this data. We want to bring the whole data from the production server to the dedicated server, so that if you search for data older than one month to find it.

  If I move with DTS the data from the production server to the dedicated server, the next DTS will overwrite or append data. But I need to add just the newly inserted records. So this is not going to work. If I run the SQL backup, I have the same options, append to media or overwrite existing media.

  All I need is to bring initially the current data and then just the new inserted ones.

  What is the best way to do this?

  Things that are not suitable for my solution:

  1. changing the application, so that data will not be deleted after one month

  2. changing the DB to Oracle

  I hope I was clear enough.

  Thanks a lot

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  September 26, 2004 at 10:12 pm

  #524444

  Changing the db to Oracle??? You won't find that suggestion here!

  If you are working on a month-by-month basis, can't you just run a DTS package at 23:59 on the last day of every month to perform the transfer/delete for the previous month?

  Eg DTS runs on 31 Oct 2004, transfers and then deletes any records where transaction date < 1 Oct 2004 etc etc.

  Or, for more-frequent updates, could it run every day and transfer/delete records where transaction date < dateadd(m, -1, getdate())?

  Regards

  Phil

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  September 27, 2004 at 2:11 am

  #524454

  I'm not sure I understand the problem.  As Phil says, at the end of the month before the cleardown for the next months data simply run the DTS to copy across the records to the dedicated box.

  You don't have to delete the existing data on your new dedicated box.

  If it is a rolling month then again, as Phil says do your scheduled transfer.

  LinkedIn Profile

• data_czar

  Ten Centuries

  Points: 1147

  More actions

  September 27, 2004 at 10:53 am

  #524548

  Couldn't you send the transaction logs to the other server and apply them there. 

• DuruG

  SSCrazy

  Points: 2089

  More actions

  September 28, 2004 at 9:47 am

  #524658

  I think you guys are right. I will run the DTS every month with append option on. Since every second are at least 10 records added, how can I make sure that I don't miss any data during the transfer?

  There are at least 20 tables and unfortunately there is no Primary Key in the main table. Don't blame it on me, blame the company who wrote the app.

  Thanks a lot