Bring data from one server to another server

Question

Bring data from one server to another server

SQL-Learner

SSCommitted

Points: 1537
More actions
January 12, 2017 at 7:15 pm

#316917

Hi,
I have a few tables in one server which contains huge amount of data lets say 50 million rows. Now I want to get data in the another server. I don't need the full data, I need to bring data for last 2 months. I need to do it for every table.
I need the best possible options to pull last two months of data from one server to another server.
Looking for help

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

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

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

SQL-Learner - Thursday, January 12, 2017 7:15 PM
Hi,
I have a few tables in one server which contains huge amount of data lets say 50 million rows. Now I want to get data in the another server. I don't need the full data, I need to bring data for last 2 months. I need to do it for every table.
I need the best possible options to pull last two months of data from one server to another server.
Looking for help

As always, it depends. Where are you server in relation to one another? What defines the Month (it's Jan. 13, 2017 so do you need all of Jan '17 and all of Dec '16 or do you need to go back and get data after Nov 13, '16 so that you have 2 full months of data)? But aside from that I think you have 3 options (in no particular order and which option you choose depends upon your environment).
1. Linked server and a job to Truncate all you Destination tables then a series of queries to populate your desired date range for each table.
2. You can run Backup and Restore to your destination server then Delete the data you don't need.
3. SSIS Package to handle option 1.
There are probably other option also, so I wait to hear what others have to say and what you choose to implement.

Regards,
Matt

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

If there are simple queries, and it's a bulk set of data, I'd use bcp to pull out the data and then push it back in.

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

SQL-Learner - Thursday, January 12, 2017 7:15 PM
Hi,
I have a few tables in one server which contains huge amount of data lets say 50 million rows. Now I want to get data in the another server. I don't need the full data, I need to bring data for last 2 months. I need to do it for every table.
I need the best possible options to pull last two months of data from one server to another server.
Looking for help

There's no easy solution to this. There are reference tables where you need everything. There will be tables that have no "DateAdded" column. And, the tables will have FKs that need to be resolved in the correct order. You're either going to have to write some code to do this or write some code that will write the code to do it.

Probably the easiest way to do it is to restore a backup and delete all but the last two months of data from the larger tables and call it a day. But that won't shrink the database and, if you do shrink it, you'll need to rebuild most of the indexes due to some really nasty fragmentation that occurs during shrinks.

--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)

SQL-Learner SSCommitted Points: 1537 More actions · Answer 4

Matt Simmons - Friday, January 13, 2017 7:35 AM
SQL-Learner - Thursday, January 12, 2017 7:15 PM
Hi,
I have a few tables in one server which contains huge amount of data lets say 50 million rows. Now I want to get data in the another server. I don't need the full data, I need to bring data for last 2 months. I need to do it for every table.
I need the best possible options to pull last two months of data from one server to another server.
Looking for help
As always, it depends. Where are you server in relation to one another? What defines the Month (it's Jan. 13, 2017 so do you need all of Jan '17 and all of Dec '16 or do you need to go back and get data after Nov 13, '16 so that you have 2 full months of data)? But aside from that I think you have 3 options (in no particular order and which option you choose depends upon your environment).
1. Linked server and a job to Truncate all you Destination tables then a series of queries to populate your desired date range for each table.
2. You can run Backup and Restore to your destination server then Delete the data you don't need.
3. SSIS Package to handle option 1.
There are probably other option also, so I wait to hear what others have to say and what you choose to implement.

Hi Matt,
Actually I have a database in a server which contains 50 Tables and each table contains some 10 million records. Now I need to get last two months data i.e. Nov 16 , Dec 16 months data. in another server for all the 50 tables.
1. What query should I write to achieve this task. If I have a created date column in all tables, how can I do this.
2. If there is no date column, how should I do it.

If I need do this with linked server. What logic should I write to achieve it.
If I need to create a SSIS package what logic should I implement to achieve this task

Any suggestions would be helpful.

Matt Simmons SSCrazy Points: 2846 More actions · Answer 5

SQL-Learner - Wednesday, January 18, 2017 1:05 PM
Matt Simmons - Friday, January 13, 2017 7:35 AM
SQL-Learner - Thursday, January 12, 2017 7:15 PM
Hi,
I have a few tables in one server which contains huge amount of data lets say 50 million rows. Now I want to get data in the another server. I don't need the full data, I need to bring data for last 2 months. I need to do it for every table.
I need the best possible options to pull last two months of data from one server to another server.
Looking for help
As always, it depends. Where are you server in relation to one another? What defines the Month (it's Jan. 13, 2017 so do you need all of Jan '17 and all of Dec '16 or do you need to go back and get data after Nov 13, '16 so that you have 2 full months of data)? But aside from that I think you have 3 options (in no particular order and which option you choose depends upon your environment).
1. Linked server and a job to Truncate all you Destination tables then a series of queries to populate your desired date range for each table.
2. You can run Backup and Restore to your destination server then Delete the data you don't need.
3. SSIS Package to handle option 1.
There are probably other option also, so I wait to hear what others have to say and what you choose to implement.
Hi Matt,
Actually I have a database in a server which contains 50 Tables and each table contains some 10 million records. Now I need to get last two months data i.e. Nov 16 , Dec 16 months data. in another server for all the 50 tables.
1. What query should I write to achieve this task. If I have a created date column in all tables, how can I do this.
2. If there is no date column, how should I do it.
If I need do this with linked server. What logic should I write to achieve it.
If I need to create a SSIS package what logic should I implement to achieve this task
Any suggestions would be helpful.

SQL_Learner... we will guide you and make recommendations but we can't code it for you.

Lookup how to create a Linked Server.
Once you have that done, create your shell DB with empty tables on the "2 Month Server" (2MS and PROD as reference to Servers).
Next you will need to populate the empty tables on your 2MS from PROD. If you don't have any relationships or constraints between tables this will be straight forward. Write 50 queries INSERT INTO Table1 (Field List) SELECT LinkedServerName.DBName.Table1 FieldList WHERE InsertedDate > DATEADD(M,-2 GETDATE()).

Hope That Helps
Regards,
Matt

Regards,
Matt