On our current project, we try to keep testing as close to as what’s going in proudction as possible. We take frequent database backups (appropriately clean some of the data) and then each developer and the QAs restore them to local copies of SQL server. The DB in production is massive with its transaction file, so here’s a way of speeding it up for local copies.

1. Restore the DB into a copy of development
2. Trim what tables/stored procedures/etc you don’t need
3. Shrink the DB
4. Detach the DB and remove the log (LDF) file (our prod log file is about 2G and even though it’s empty when we back up, it takes an excessive time to restore)
5. Reattch the DB and back that up
6. Restore whenever you want and it should be faster