September 4, 2013 at 6:43 am
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.
September 5, 2013 at 3:55 am
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
September 5, 2013 at 4:00 am
What's wrong with restoring a backup? It's the easiest method.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 5, 2013 at 4:15 am
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
September 6, 2013 at 7:23 am
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.
September 6, 2013 at 4:59 pm
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
September 11, 2013 at 8:13 am
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.
September 11, 2013 at 9:22 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply