Home Forums SQL Server 2008 SQL Server 2008 High Availability What are ways of Exporting data of Wahehouse Database from Production to Development Server? RE: What are ways of Exporting data of Wahehouse Database from Production to Development Server?

  • 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