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

This can be scripted using SQL and scheduled as a job on the server to execute once an hour. Since you have a backup copy, I assume, that is pristine, then all you need to do is take the database offline, restore from the backup, bring the database back online. All of this can be scripted. Do you need the scripts?

Answer 2

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

Answer 3

You could also detach the database, overwrite the data and log files from your template (previously detached) and then re-attach.