SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Approach to Archieve DB


Best Approach to Archieve DB

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221634 Visits: 42003
runal_jagtap (3/18/2013)
4 View ... 0 replies w00t


14 minutes... broad subject. Need more patience. ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221634 Visits: 42003
runal_jagtap (3/18/2013)
Example -

We have a DB which have loads of data & we want to archiev it to some Server in such a way that

Scenario - Source DB have 5 yrs Data in it, we want to move the 5 yrs Data to Destination DB, & then will keep only one month data in Source DB, that means on monthly basis we will have less data in Sorce DB.


Please help me a best approach??


Will you ever have the need to access the data that's more than a month old through a GUI or stored procedure?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 692
Jeff, No we would never need a data a month old, incase if someone needs the data we can retrieve it from Archive DB..
but whats the best approach???w00t

************************************
Every Dog has a Tail !!!!! :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221634 Visits: 42003
One very effective approach would be to "partition" the table but that's only if you have the Enterprise Edition and, IIRC, if you don't mind the "archive" being in the same database. What that will do for you is make it almost instant to "archive" a previous month and it will reduce the nightly maintenance because you won't have to rebuild or reorganize the idexes for the entire table because, if you do it right, the indexes are partitioned, as well.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 692
Thanks Jeff :-)

Currently me planning to go with SSIS Package Automation...
Well see how i succedd.. incase any query i will come here again.. stating

65 views & 0 replies w00t ( :-D )

************************************
Every Dog has a Tail !!!!! :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221634 Visits: 42003
runal_jagtap (3/19/2013)
Thanks Jeff :-)

Currently me planning to go with SSIS Package Automation...
Well see how i succedd.. incase any query i will come here again.. stating

65 views & 0 replies w00t ( :-D )


So, what's the plan? Will the archive table be in a different database or the same database> I'm asking because although SSIS will certainly do the job, it seems like real overkill.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 692
Jeff, Yup The Srever & the Database would be different...

Why it would be overkill w00t explain me.. coz i am going to do it.. i dont know how to do it..

************************************
Every Dog has a Tail !!!!! :-D
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 1204
SSIS is normally utilised when the copy/move is complex, if it involves data transformations, validations etc.

'Only he who wanders finds new paths'
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 692
My Task is mentioned belwo..

Copy all data from Source to destination DB
Every month only the updated or new data needs to be transffered to destination..



Will SSIS help in this?

************************************
Every Dog has a Tail !!!!! :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221634 Visits: 42003
runal_jagtap (3/21/2013)
Jeff, Yup The Srever & the Database would be different...

Why it would be overkill w00t explain me.. coz i am going to do it.. i dont know how to do it..


If you've already decided to use SSIS, then my suggestion of making a simple job do a simple insert to the archive and a simple delete from the source would probably underwhelm everyone's gumption. Like I said, doing this through SSIS is real overkill.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search