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
OnlyOneRJ
OnlyOneRJ
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 692
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??

************************************
Every Dog has a Tail !!!!! :-D
OnlyOneRJ
OnlyOneRJ
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 692
4 View ... 0 replies w00t

************************************
Every Dog has a Tail !!!!! :-D
david.alcock
david.alcock
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1195
We have done similar things in the past, essentially using SSIS to archive/move data to a new server. On enterprise you could partition the data instead.

'Only he who wanders finds new paths'
OnlyOneRJ
OnlyOneRJ
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 692
David, can you tell me how did u perform this activity via SSIS... please explain in details

************************************
Every Dog has a Tail !!!!! :-D
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54534 Visits: 32769
If you can make changes to your architecture, you might want to look into partitioning the data. That makes it very easy to move data out of one database and into another. That's pretty much what it's designed to do. But, it has a number of caveats, the biggest being that you can ensure that retrieval of your data is always going to filter by the same structure that you use to partition the data. If you can't guarantee that, I wouldn't suggest this approach. Otherwise, it's a very viable method. This is an introductory article from Microsoft. There are other resources online to learn more.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
OnlyOneRJ
OnlyOneRJ
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 692
I did a Import export activity for transfeering the Tables/data to other DB/server and saved the Packgae as SSIS package to the File Location..

Now how do i move ahead...

1) Tables & data imported to destination server via import export
2) SSIS package stored in some location.


So the destination server have till date data from source server..
after a month how should i nmake changes & execute the package, so that the Only Updated or new data should be imported to destination server w00t

************************************
Every Dog has a Tail !!!!! :-D
david.alcock
david.alcock
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1195
Apologies, I am not sure what you are asking.

If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.

As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.

'Only he who wanders finds new paths'
OnlyOneRJ
OnlyOneRJ
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 692
david.alcock (3/18/2013)
Apologies, I am not sure what you are asking.

If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.

As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.



David, can you tell me how did u perform this activity via SSIS... please explain in details

************************************
Every Dog has a Tail !!!!! :-D
Vedran Kesegic
Vedran Kesegic
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 1266
Grant Fritchey (3/18/2013)
If you can make changes to your architecture, you might want to look into partitioning the data.

You can't switch partition to a switching table that is in different database. Switching table and partition must reside on the same filegroup in order for switch partitions to work.
The only benefit would be much faster delete (drop partition) of the data you already copied - worth using.
I would do a partition switch, bcp-out switching table to a file in native format, bcp-in that file to destination db switching table, and then switch that into destination partitioned table. That is by far the fastest method.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

david.alcock
david.alcock
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1195
Well we were mainly moving to new structures so the migration was two part, moving recent data to a faster server and the older data shifted to cheaper archive storage. SSIS was utilised in very simple form, a data flow with source, destination and lookup tasks were the main components as well as script tasks for some customisation. Lookup tasks are very handy for incremental loads as you may be doing.

Highly recommend you give this a read if you havent already: http://www.sqlservercentral.com/articles/Stairway+Series/76390/

We didnt have enterprise otherwise I would have implemented some form of partitioning for sure.

'Only he who wanders finds new paths'
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