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