• 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.

    One possibility is to mix differential backups and restores with the full backups and restores. This makes sure you get all the data but allows for overall faster processing.

    Periodically -- weekly, biweekly, whatever -- you do a full backup. Naturally this can be done when the most time is available. This provides a "base" to apply differentials to. Then do daily -- or whatever -- differential backups that you can apply to the base to bring the dev and qa dbs up to date.

    First, restore the "base" backup to a different, temporary "restore db name", using WITH NORECOVERY. Then apply the differential (if any). RESTORE the restore db name WITH RECOVERY. Run any required scripts (adjust permissions, resync users, etc.). For example, for "DB1", restore to "DB1_Restoring".

    Once the restored db is verified fully ready to go, drop the existing db and rename the new one to the main name; for example, DROP DB1, RENAME DB1_Restoring to DB1.

    Finally, immediately "pre-restore" the "base" backup to the "restore db name" (WITH NORECOVERY). That reduces the time needed for the next refresh, because the bulk of the data has already been restored.

    Note that this also allows you to quickly make the dev or qa db look like prod using the standard differential backup method if you need to immediately investigate a prod problem.

    It also leaves the original dev and qa dbs available until the new db restore is verified as working.

    The big disadvantage with this method is that it requires more disk space ... I fall back on "disk is (relatively) cheap" there :-).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.