Archive some records from db

Question

Archive some records from db

VRT

SSCertifiable

Points: 5776
More actions
January 6, 2009 at 12:01 am

#195709

Hi,
I want to archive(take a backup and keep it in new db) records of the year 1999 and I want to delete them from the main prodcution database. Please tell me how can I do this.
Can I take the backup of records only? or I have to take the backup of whole Database?
Thanks a lot,
Venki
Thank You

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Backups are of a complete database or filegroup.

I would suggest creating the table(s) in the new database and, if on the same server, select the rows from the original table(s) into the new table(s) and then do delete from the original tables where exists (Select 1 from new_table where old_table.primary_key = new_table.primary_key.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

noeld SSC Guru Points: 96590 More actions · Answer 2

Replication is definitely not the tool for what you want.

* Noel

Old McDonald SSCertifiable Points: 5768 More actions · Answer 3

Select into and then deleting is an option but beware to fire these queries if the number of records in your table is huge. Delete might take ages.

Can you answer these questions:

How many records do you have in total?

How many records you want archive?

-Vikas Bindra

renu.ap SSC Journeyman Points: 83 More actions · Answer 4

Im having similar issue. log file at subscriber grows larger while archiving data at publisher.

giving large head ache.

pls suggest soln.......

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Jack Corbett already suggested the correct solution. If the deletes are huge, then you need to write a loop to do the deletes in groups of 10 or 20,000 rows at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 6

renu.ap (1/9/2009)
Im having similar issue. log file at subscriber grows larger while archiving data at publisher.
giving large head ache.
pls suggest soln.......

if transactional integrity is not a problem at susbcriber take a look at MaxcmdinTran parameter.

* Noel