The best way to automatically restore a database every hour

Question

I have a demo site where anyone can log in and test the management interface.

Every hour, I would like to clear all the data in the SQL 2008 database and restore it from the original.

Red Gate Software has some awesome tools for this, but they are now out of my budget.

Can I just backup the database data file and then create a C # console application that will delete it and copy over the original. Then I may have a window schedule task to run .exe every hour.

It's simple and free ... will it work?

I am using the web version of SQL Server 2008 R2

I understand that Red Gate Software is technically better, because I can configure it to analyze db and only update records that have been changed, and the approach that I have above is similar to a “sledgehammer”.

Answer 1

It's simple and free ... will it work?

Yes, you could do it like this, just remember to put the database in single-user mode before restoring it, otherwise your recovery will fail

script example

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 2

This could be a script using SQL and is scheduled as a job on the server to run once per hour. Since you have a backup, I assume that it is untouched, then you only need to take the database offline, restore from the backup, return the database online. All of this can be scripted. Do you need scripts?

Answer 3

You can also detach the database, overwrite the data and log files from your template (previously disconnected), and then reconnect.