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

Refresh tables from production to Dev Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 94, Visits: 754
Hi,

I have a requirement to refresh some tables in dev from prod...and we need to do this every week...so i need to automate...

I thought of using SSIS, but never did it....if anyone did please let me know the process or your suggestions.

Thanks
Post #1453089
Posted Wednesday, May 15, 2013 7:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:23 AM
Points: 384, Visits: 1,264
Robin35 (5/15/2013)
Hi,

I have a requirement to refresh some tables in dev from prod...and we need to do this every week...so i need to automate...

I thought of using SSIS, but never did it....if anyone did please let me know the process or your suggestions.

Thanks


When you mean refresh, you mean drop and recreate the whole table? Are you keeping some records, if that's the case, how many? ... how big the table is or will be? ...

SSIS maybe faster but also more difficult to maintain.

Depending of how many records and where those two instances are, you may consider bcp as well. You export via bcp, then import via bcp. You can put that logic inside a job and there you go, you have it scheduled.

If you need to wipe out the whole table, you can drop indexes prior import, truncate, and re-import. Take a look on dependencies though, like constraints or identity keys. That may be an issue when refreshing the table, even with SSIS.

If you go that route, here's a good link or article for bcp: https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/
Post #1453108
Posted Wednesday, May 15, 2013 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 273, Visits: 856
Do you intend to move all objects AND all data?
Do you have constraints on having prod data in DEV? (security etc.)

You may need processes that obfuscate data that cannot be viewed by developers.
Post #1453199
Posted Wednesday, May 15, 2013 11:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 94, Visits: 754
Thanks for the response...

Never mind our requirement has changed and we are going to do full db restore...

Thanks again
Post #1453240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse