SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What are ways of Exporting data of Wahehouse Database from Production to Development Server?


What are ways of Exporting data of Wahehouse Database from Production to Development Server?

Author
Message
harsimranjeetsinghwasson
harsimranjeetsinghwasson
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 239
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.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 1262
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61433 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 1262
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
sqlslacker
sqlslacker
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 209
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.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 1262
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
harsimranjeetsinghwasson
harsimranjeetsinghwasson
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 239
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.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 1262
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search