0808 (8/14/2008)

---

Here's what we did: Setup a daily scheduled OS task (not Agent!) that runs a .BAT file.

This .BAT file does this:

1. runs a SQL script that:

- sets single user

- drops the development DB

- creates an empty development DB

2. uses "net stop" commands to stop SQL Server services. Order is important, e.g. stop Agent before stopping sql engine)

3. does an OS copy of the read only database files (MDFs and LDFs). e.g.:

- copy RO_database.mdf Dev_database.mdf

- copy RO_database.ldf Dev_database.ldf

4. restarts SQL services (in reverse order from #4)

5. runs a sql script to "activate" the dev database

- EXEC SP_DBOPTION 'Dev_database', 'read only', 'false'

- EXEC SP_DBOPTION 'Dev_database', 'dbo use only', 'false'

- EXEC sp_change_users_login 'Auto_Fix', 'username'

- ALTER DATABASE Dev_database SET RECOVERY SIMPLE

Note that I was having some timing problems on some of the steps above, with certain tasks sometimes failing because the previous step wasn't completely finished. Those problems disappeared once I put a 5 second sleep/delay between every step.

It was a bit of a hassle to setup, but requires no ongoing maintenance. My Dev database is refreshed from production every day. A potential drawback is that the SQL server is completely down for a while each night. This wasn't a problem for us.