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

What are ways of Exporting data of Wahehouse Database from Production to Development Server? Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
Hi,

I would like to export data of warehouse from Production to dev for special reasons, what are best possible
ways for the same.


We normally have a job that runs in dev and refresh database from backup in production.

But would like to export data from production to dev in an another way.


I thought Log shipping or Mirroring or Replication might be one instead of using Export Import Wizard.

as database size is about 100 GB.
Post #1491259
Posted Thursday, September 5, 2013 3:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 313, Visits: 584
harsimranjeetsinghwasson (9/4/2013)
Hi,

I would like to export data of warehouse from Production to dev for special reasons, what are best possible
ways for the same.


We normally have a job that runs in dev and refresh database from backup in production.

But would like to export data from production to dev in an another way.


I thought Log shipping or Mirroring or Replication might be one instead of using Export Import Wizard.

as database size is about 100 GB.


Mirroring would provide you with a full copy of the database, you would need to create snapshots to view the data, as only the prinicipal is readable in a mirror. SQL 2012 Always on availability groups provides the functionality for readable mirrors, but SDQL 2008 \ R2 does not.

Log Shipping would again provide you a readable copy of the full database but would require you to restore the transactions logs using the standby recovery option. This also means that while you restore your logs the DB is unavailable.

Transactional Replication for real time data or snapshot replication for point in time data may be an option and you could create a publication(s) with the articles you need.

Alternativly creating a SSIS Package or Packages to perfom the ETL transfer of the data.


MCITP SQL 2005, MCSA SQL 2012
Post #1491692
Posted Thursday, September 5, 2013 4:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
What's wrong with restoring a backup? It's the easiest method.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1491693
Posted Thursday, September 5, 2013 4:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 313, Visits: 584
I suspect its because the DB is 100GB in size. And they may only need some not all of the tables in the database.

MCITP SQL 2005, MCSA SQL 2012
Post #1491695
Posted Friday, September 6, 2013 7:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:37 AM
Points: 50, Visits: 194
RTaylor2208 (9/5/2013)
I suspect its because the DB is 100GB in size. And they may only need some not all of the tables in the database.


The size is irrelevant. A restore is going to be faster and easier whether you're talking 100MB or 100 TB, as long as you're not intending to change much of the data/structures immediately afterwards.
Post #1492221
Posted Friday, September 6, 2013 4:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 313, Visits: 584
That's a very sweeping statement. Without any details on the environment they have you cannot definitively say that a restore will be quicker than one of the afore mentioned options. All the op has posted is that the production database is 100GB in size no mention of how much of that data is required in the development environment,if any transformation is required, whether is real time, closer to real time or can be hours/days/weeks apart.

I work with OLTP SQL databases which are several TB's in size that we pull data from daily to source our warehouse, data marts, cubes and non production environments, those processes are hundreds of times quicker than restoring a full backup.


MCITP SQL 2005, MCSA SQL 2012
Post #1492460
Posted Wednesday, September 11, 2013 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:55 AM
Points: 49, Visits: 194
Appreciated your views,

And yes there might be case when i need only few tables data which will be faster than a fullbackup.

But my main concern for same was that i need production data only and if restore backup from production

my new changes on dev that are to be deployed on production will be overwritten or rollback to previous state .

I'll try your option and will revert you my feedback.
Post #1493702
Posted Wednesday, September 11, 2013 9:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 313, Visits: 584
If I understand you correctly your main concern with restoring the production database onto your development enviornment is that you will overwrite changes made whilst in the development stages.

You have a few options.

1. Restore your full backup from production and make sure all changes that are part of your development are scripted so you can re-apply them post restore. Easily enough done in an automated fashion using sqlcmd / powershell / 3rd Party tools.

2. If only data has changed use SSIS to transfer the data from production tables to a staging area then merge your data, this can be done using the T-SQL MERGE statements. Fiarly involved if you have a large number or tables.

3. If your also making DDL changes at your development environment you will need to have modification scripts that you can run in addition to point 2 above to modify the structure to suit inaddtion to merging the data. This will be more cumbersome and will require regular maintenance.

4. One last option is to look at the redgate comparision tools or the native table diff utility.

It will all come down to the volume of changes you anticiptae, the frequency you want to perform them and whether the ROI on developing the solution is better than going down the full backup and restore route.


MCITP SQL 2005, MCSA SQL 2012
Post #1493759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse