Copy DB Tables from PRod to DEV

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Copy DB Tables from PRod to DEV

12 posts, Page 1 of 2

1 2 »»

Copy DB Tables from PRod to DEV

Rate Topic

Display Mode

Topic Options

Author

Message

poratips

Posted Monday, November 12, 2012 6:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 112, Visits: 435

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

anastke

Posted Tuesday, November 13, 2012 3:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:14 AM
Points: 81, Visits: 1,515

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

anthony.green

Posted Tuesday, November 13, 2012 3:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

poratips (11/12/2012)

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

poratips

Posted Wednesday, November 14, 2012 6:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 112, Visits: 435

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

Thanks so much!

Post #1384594

Jeff Moden

Posted Wednesday, November 14, 2012 5:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 PM
Points: 32,910, Visits: 26,804

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1384941

poratips

Posted Tuesday, November 20, 2012 9:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 112, Visits: 435

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

Steven Willis

Posted Thursday, November 22, 2012 11:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:19 AM
Points: 283, Visits: 1,239

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

ACinKC

Posted Monday, November 26, 2012 8:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 21, 2013 3:53 PM
Points: 114, Visits: 916

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

Sigerson

Posted Tuesday, November 27, 2012 9:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 174, Visits: 550

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

rohit-kalmankar

Posted Wednesday, November 28, 2012 6:54 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:00 AM
Points: 50, Visits: 26

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions