John Mitchell-245523 (12/31/2012)
ScottPletcher (12/28/2012)
I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.
John
My big concern is that the QA or staging env would accidentally load data to production. But there's also:
the amount of time, resources and locking that can occur while loading data;
the extra processing load put on the non-prod systems;
potentially poor timing of the load/update jobs running -- QA could be in the middle of a critical demo or test.
Source issues could certainly be valid. In our case, I have separate source-only backups that I can run on non-prod environments. We also have source mgmt software outside of the db itself that controls source and source versioning. You may have to take additional steps to secure your source separately prior to the restore.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.