Best way to auto-restore a database every hour

Question

I have a demo site where anyone can login and test a management interface.

Every hour I would like to flush all the data in the SQL 2008 Database and restore it from the original.

Red Gate Software has some awesome tools for this, however they are beyond my budget right now.

Could I simply make a backup copy of the database's data file, then have a c# console app that deletes it and copies over the original. Then I can have a windows schedule task to run the .exe every hour.

It's simple and free... would this work?

I'm using SQL Server 2008 R2 Web edition

I understand that Red Gate Software is technically better because I can set it to analyze the db and only update the records that were altered, and the approach I have above is like a "sledge hammer".

Answer 1

It's simple and free... would this work?

Yes, you could do it like that, just remember to put the DB in single user mode before restoring it otherwise your restore will fail

example script

USE master
GO

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO



RESTORE DATABASE YourDB FROM DISK=N'D:\Backup\Pristine.BAK' WITH  FILE = 1,  
NOUNLOAD,  REPLACE,  STATS = 10
GO

ALTER DATABASE YourDB SET MULTI_USER
GO