SQL Server Performance Forum – Threads Archive

backup data

I have a system that has two databases. One is for reporting/archiving and the other is for production. The production one needs to stay lean with only the data needed for a couple days production (it’s a distribution center, with carton lookups for diverting that need to happen extremely fast). Therefore, i created an archive database and put everything over there that is "out of the system", for reporting, and the customer requested that we keep things for 14 months. This database is very large (60Gigs with around 60 million records). My question is, is there a good way to move records to the archive db? I was unable to figure out how to do it when i tried a year ago because i didn’t have time to mess around. We are doing an expansion now though, and i would like to do it right. Currently, i have a program that looks at the production db, finds cartons that have changed in the past half hour (we have a datetimestamp column on these records that it interrogates), and then writes them to a txt file. Another program then reads these and posts them to the archive db. This has worked fine (most of the time), but i would like to have SQL do this for me. I’m sure there’s a way, but have been having trouble finding it. I was going to use DTS, but the query wouldn’t let me select the records that i wanted. Another caveat is that i need the supporting tables there first, so i don’t get foreign key violations. Is there a way to have SQL jsut transfer over the records that have changed since the last time it transfered records? Let me know if you need more information. Thanks in advance, Ben

Have you tried using BCP during less traffic hours on database?
We have similar database that is used 24/7 and have to archive 1 week old data to archive database, we used BCP without any problem. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

can you make it do a selective copy (just copy the stuff that’s changed since last night? I don’t want to copy all 2 million rows nightly. i think it will be easy enough for me to call this utility from my program. Would i make a call from my program for each table that i wanted to copy, and in the correct order? Thanks again, Ben

Take a look of ours sponsors, Red Gate has Bundle tool for that.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

quote:Currently, i have a program that looks at the production db, finds cartons that have changed in the past half hour (we have a datetimestamp column on these records that it interrogates), and then writes them to a txt file. Another program then reads these and posts them to the archive db. This has worked fine (most of the time), but i would like to have SQL do this for me. I’m sure there’s a way, but have been having trouble finding it.

SQL is used from program to "find cartons that have changed in the past half hour" anyway, so I don’t understand how it is different to do it directly in the script. DTS can do it too, but to give you recommendation we need more details, like source and destination tables structure. You write changes into archive db, so you must store history of data in production db, which means archive db pks must include timestamp columns… and so on. I can just guess without more details. Without them the code for one table could be:
insert into archive.dbo.destinationTable (…)
select …
from production.dbo.sourceTable src
where src.dateTimeStamp > @dateTimeStamp About handling foreign key constraints, you have to insert data in specific order, first in parent tables then in child tables. You can use a script for that if data are on the same server or you can use DTS with only select part of the query above.

So you’re saying, when i’m doing the DTS export/import thing, to select the option that "uses a query to specify teh data to transfer" and put code in similar to what your example showed above? If I just created the script to be in the correct order by putting the parent tables first, then the child tables under that, would it do the insert for everything in the first select statement, then move down to the next. For example, in my DB, table LocType (LocTypeID PK, Description) is a parent of the Location table (LocTypeID PK, LocationID PK, Description, Active, LocationStatusID, DateTimeStamp), which is a parent of the Carton table (CartonID PK, StatusID, LocTypeID, LocationID, …there’s a lot of columns in this table). So if I made the script look like this: insert into archive.dbo.loctype select * from production.dbo.loctype (i don’t have a datetimestamp on this table, and it is small enough i can transfer it everytime) insert into archive.dbo.location select * from production.dbo.location src where src.dateTimeStamp > @dateTimeStamp insert into archive.dbo.carton select * from production.dbo.carton src where src.dateTimeStamp > @dateTimeStamp that would work to copy the changed data without violation the foreign key constraints? My last question is how to make sure the &datetimestamp is correct? I know i could calculate it to just be 35 minutes back, but what if sql was stopped for some reason, and it was longer than 35 minutes since the last run of DTS? Is there a way to pull this value off of the last DTS run so that i make sure i don’t have any holes in my data? Thank you very much, Ben

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by boutwater My last question is how to make sure the &datetimestamp is correct? I know i could calculate it to just be 35 minutes back, but what if sql was stopped for some reason, and it was longer than 35 minutes since the last run of DTS? Is there a way to pull this value off of the last DTS run so that i make sure i don’t have any holes in my data? <hr height="1" noshade id="quote"></blockquote id="quote">Create process controll structure and each time you start the process read the last date time stamp you moved. Then start from that time stamp. Just to be sure not to miss something or load the same data twice I would put condition dateTimeStamp >= @PreviousTimeStamp and dateTimeStamp < @nextTimeStamp. I would make sure that @nexttimeStamp is populated at the beginning of the process and maybe make it a minute earliear then getDate(), because I don’t know how do you populate your dateTimeStamp columns and if they are dateTime or smallDateTime. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Thank you very much<hr height="1" noshade id="quote"></blockquote id="quote">You are wellcome. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Thanks for the help with the datetimestamp stuff. Do you have any thoughts to the other part of that question? The one about foreign keys and the select statments, etc. Thanks, Ben

quote

o you have any thoughts to the other part of that question? The one about foreign keys and the select statments, etc.

It can be done the way you described. This the the only way to do it without droping or disabling fk constaints.

So the only thing that i am still confused about is how/where would i store the datetimestamp, and update it. Would i create another table to store this, or is there a default table. I understand that @datetimestamp is a variable that gets passed, but i’m not sure how to update it/store it and call it out. If i had my script that at the beginning got the old timestamp, used it in my function, then updated it, I should be fine. I just need to know how to do this. Here is what i was thinking for the script: /* need help with this top part for the datetimestamp stuff*/ then ..more tables insert into archive.dbo.loctype select * from production.dbo.loctype (i don’t have a datetimestamp on this table, and it is small enough i can transfer it everytime) insert into archive.dbo.location select * from production.dbo.location src where src.dateTimeStamp > @dateTimeStamp insert into archive.dbo.carton select * from production.dbo.carton src where src.dateTimeStamp > @dateTimeStamp ..more tables the datetimestamp on one table is of type datetime (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> all the rest are varchar{20} Thanks in advance, Ben

Sorry Ben, I managed to miss your post somehow. Here is the draft of the process: 1. Select @startDateTime = endTime from controlTable –add where clause or top 1 order by @endTime if you want to keep history.
2. set @endTime = getDate()
3. update controlTable set endTime = @endTime –or insert into if you want to keep history
4. insert into destinationTable(…)
select …
from sourceTable
where dateTimeStamp >= @startDateTime and dateTimeStamp < @endTime You may want to use transaction around or to update status when process finishes, so if it fails you can redo the action.

]]>