Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Copy DB Tables from PRod to DEV Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 6:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Hello,
I have situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.

Appreciate your help!

Thanks,
Post #1383662
Posted Tuesday, November 13, 2012 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 135, Visits: 2,069
maybe something like

insert into devsrv.devdb.dbo.devtab (col1, ... coln)
select col1,... coln from prodsrv.proddb.dbo.prodtab
where datecol between starttime and endtime

Post #1383997
Posted Tuesday, November 13, 2012 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,143, Visits: 4,942
poratips (11/12/2012)
Hello,
I have situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.

Appreciate your help!

Thanks,


I would create a custom SSIS package which loads the tables in full that you need and then runs the queries you need to export only the sub set of the data.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1383998
Posted Wednesday, November 14, 2012 6:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Thanks.
I have created Linked Server and use the script Insert INTO...Select * from ... Where...


Thanks so much!
Post #1384594
Posted Wednesday, November 14, 2012 5:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
poratips (11/14/2012)
Thanks.
I have created Linked Server and use the script Insert INTO...Select * from ... Where...


Thanks so much!


Did you make sure the linked server is "read only"?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1384941
Posted Tuesday, November 20, 2012 9:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Thanks Jeff, This is a very nice tip, i will make sure it.

Thank you so much for all great efforts to help!

Post #1387223
Posted Thursday, November 22, 2012 11:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I know this seems after-the-fact now, but if you don't have sufficient administrator privileges (such as on a public shared server like GoDaddy) you can still copy the tables and data you want. Right-click on the database to copy and select Tasks|Generate Scripts. The rest is pretty much self-explanatory. Use the Advanced Options to fine-tune exactly what you want such as schema only or schema with data.

Yes, this is a brute force run-once import method, but once you get the tables and data scripted then you can run the scripts on your local development server and then you have what you need. I'm often stuck working on shared servers where most of the sysadmin functions are denied to me and Generate Scripts has bailed me out more than once. I would also consider it the "poor man's" backup too for such situations where asking for a restore can cost $150/hr. In many cases a weeks-old (or even months-old) backup scripted out and saved to DVD can save a client from losing everything.

 
Post #1387964
Posted Monday, November 26, 2012 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:27 PM
Points: 115, Visits: 957
Also, hopefully you've considered if there is any private data (names, addresses, DOBs, etc.) in the Prod database that would have to be scrubbed before landing in a potentially unsecure development environment!
Post #1388665
Posted Tuesday, November 27, 2012 9:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
Jeff Moden (11/14/2012)


Did you make sure the linked server is "read only"?



How can you tell whether a Linked Server is read only. I looked for a 'read-only' flag and didn't find it under that name. I am linking a case management system to our main accounting system and I need to *sure* that I won't update accounting. Ever.


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1389294
Posted Wednesday, November 28, 2012 6:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:37 AM
Points: 92, Visits: 34
I suggest you to use a task Import/Export data task, this will suffice you requirement, I suppose.

Note : It internally creates a SSIS package which you can have a look at also.
Post #1389741
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse