I have two SQL 2005 database servers, production and development. I need to write a SSIS package to mirror data from the prod to the dev server for testing with real data.
What is the best way to do it , I want only new data from that day to be mirrored every night, because of the database size(huge).
1) I can use the built in wizard to copy the database
2) write my own Data flow tasks with a query to pick data pertaining to that day. ( problem : some tables do not have date time fields)
Options and suggestions .................