Automatically replace dev database with live database?

Question

I have live and dev versions of an ASP.NET website, with corresponding live and dev versions of the SQL Server back end. Each is on its own server (so 4 total) on the same local network.

Every so often, we've been manually replacing the dev DB with the live DB, using backup & restore, so that the dev DB is a reasonably recent snapshot of the live DB. What's the best way to automate that process?

I'd be interested in either having it run on a schedule, or making it a pushbutton process where we still control the timing.

(For what it's worth, we use CruiseControl.net; so an option that would let us use the CC.net dashboard to launch this process would be nice.)

Answer 1

I think scheduling or automating a restore would be relatively simple with the RESTORE command in T-SQL. Schedule your backups to go to a designated location and set whatever schedule / script that refreshes the dev database to look in the same location.

Answer 2

Why not set up a Snapshot replication between your live and dev database? This is the way we handle keep our dev database in synch with our production database.

http://msdn.microsoft.com/en-us/library/ms151198.aspx

Answer 3

1- you can use replication (transactional or snapshot) for synchronizing two databases.

2- write an application that get backup from db and restore it. (use SMO)

3- write an application that delete all data from dev db and copy all data from live db to it. you can use bcp (SQLBulkCopy) for doing this work.