SQL Server Database Restore Hourly

Question

Hi

I run a software platform and am thinking of setting up a demo site so that people can login into a pretend site and edit the data, etc...

However I want then the database to 'reset' every 'x hours / days' is there a way sql server can do this itself? Otherwise I will have to code an application to restore all the table data and that will take a lot of work.

Thanks

Answer 1

A SQL Server Agent job can do this. In Management Studio, create a new job, and paste in your restore query, e.g.:

RESTORE DATABASE [test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Answer 2

Yes, you create a database snapshot of the 'clean' state then every hour you revert the 'dirty' database from the clean snapshot. See:

• How to: Create a Database Snapshot (Transact-SQL)
• How to: Revert a Database to a Database Snapshot (Transact-SQL)

While other methods also exists (backup/restore, detach/attach and copy files etc) the snapshot based one is probably the fastest because snapshots are diferential based at the file system (they use Sparse Files). If your database in 'clean' state is very small (no data in tables) but then it gets much larger during that one hour then it may be simpler or faster to rely on plain backup/restore.

Also Database Snapshots are only available in Enterprise Edition of SQL Server, so if your demo uses a lower edition (Standard, Web, Express) you must use a backup/restore or an attach/detach and file copy based solution.

Example of a attach/detach file copy based solution:

1. You have saved the clean db, its files are clean_db.mdf and clean_db_log.ldf.
2. The current dirty db is online, its files are dirty1_db.mdf and dirty1_db_log.ldf
3. You copy clean_db.mdf to dirty2_db.mdf and clean_db_log.ldf to dirty2_db_log.ldf
4. You detach the database by runing sp_detach_db '<dbname>';
5. You attach the new files: sp_attach_db '<dbname>', 'dirty2_db.mdf', 'dirty2_db_log.ldf';
6. You delete the old files dirty1_db.mdf and dirty1_db_log.ldf

This procedures reduces the downtime between detach and attach by copying the clean files before the detach/attach operation.

Answer 3

You need combine the T-SQL batch for database restore and the creation of Job.

Answer 4

It's been a while since i've worked with SQL server, but if memory serves be correctly, you can use SSIS in SQL 2005/2008 (or DTS if you're still on 2000) to delete the data and import from a text/csv/xml file into the database every hour or whatever schedule you want.

http://www.codeproject.com/KB/aspnet/Schedule%5F%5FRun%5F%5FSSIS%5F%5FDTS.aspx

That will give you a step by step for setting up the job. You'll need to determine what actions happen during the process though.

This will also turn out to be a lot easier than coding an application because, well, it's already been coded for you.