Copy DB Tables from PRod to DEV

Question

Post reply

Copy DB Tables from PRod to DEV

poratips

Hall of Fame

Points: 3994
More actions
November 12, 2012 at 6:27 am

#100496

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,

Viewing 12 posts - 1 through 11 (of 11 total)

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

anastke SSChasing Mays Points: 632 More actions · Answer 1

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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.

poratips Hall of Fame Points: 3994 More actions · Answer 3

Thanks.

I have created Linked Server and use the script Insert INTO...Select * from ... Where...

Thanks so much!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

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.

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)

poratips Hall of Fame Points: 3994 More actions · Answer 5

Thanks Jeff, This is a very nice tip, i will make sure it.

Thank you so much for all great efforts to help!

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 6

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.

ACinAZ SSCommitted Points: 1849 More actions · Answer 7

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!

Sigerson Hall of Fame Points: 3697 More actions · Answer 8

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

rohit-kalmankar SSC Enthusiast Points: 196 More actions · Answer 9

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.

sstalker 94512 SSC Rookie Points: 35 More actions · Answer 10

Have you considered bcp http://msdn.microsoft.com/en-us/library/ms162802.aspx?

Arjen Krap SSC-Addicted Points: 473 More actions · Answer 11

Sigerson (11/27/2012)
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.

By making sure the account used for logging on to the other server only has read permissions.