<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Best Approach to Archieve DB
34 posts, Page 1 of 4
1
2
3
4
»
»»
Best Approach to Archieve DB
Rate Topic
Display Mode
Topic Options
Author
Message
OnlyOneRJ
OnlyOneRJ
Posted Monday, March 18, 2013 3:39 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158,
Visits: 443
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 !!!!!
Post #1432123
OnlyOneRJ
OnlyOneRJ
Posted Monday, March 18, 2013 3:52 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158,
Visits: 443
4 View ... 0 replies
************************************
Every Dog has a Tail !!!!!
Post #1432124
david.alcock
david.alcock
Posted Monday, March 18, 2013 4:00 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 117,
Visits: 454
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'
Post #1432125
OnlyOneRJ
OnlyOneRJ
Posted Monday, March 18, 2013 4:35 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158,
Visits: 443
David, can you tell me how did u perform this activity via SSIS... please explain in details
************************************
Every Dog has a Tail !!!!!
Post #1432132
Grant Fritchey
Grant Fritchey
Posted Monday, March 18, 2013 5:12 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383,
Visits: 25,189
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1432139
OnlyOneRJ
OnlyOneRJ
Posted Monday, March 18, 2013 6:15 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158,
Visits: 443
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
************************************
Every Dog has a Tail !!!!!
Post #1432161
david.alcock
david.alcock
Posted Monday, March 18, 2013 8:10 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 117,
Visits: 454
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'
Post #1432197
OnlyOneRJ
OnlyOneRJ
Posted Monday, March 18, 2013 8:43 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158,
Visits: 443
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 !!!!!
Post #1432213
Vedran Kesegic
Vedran Kesegic
Posted Monday, March 18, 2013 8:50 AM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 343,
Visits: 1,077
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.
_____________________________________________________
XDetails Addin
- for SQL Developers and DBA
blog.sqlxdetails.com
- Transaction log myths - debunked!
Post #1432217
david.alcock
david.alcock
Posted Monday, March 18, 2013 2:55 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 117,
Visits: 454
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'
Post #1432351
« Prev Topic
|
Next Topic »
34 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
