design over matter

Recently, I needed to take a snapshot of a database for my client.  We are setting up a demo site which anyone can access and modify data.  We want the database to reset to it's previous state every 24 hours.  This would remove any garbage records created by potential customers taking the application for a test drive.

My development machine is running SQL Server 2005 Enterprise Edition and I used the "Database Snapshot" feature to create my snapshot, then scheduled a job to run daily at midnight to reset the database.  Works great.  The problem is my client is running the less expensive SQL Server 2005 Workgroup Edition.

From what I found online, the workgroup edition is the low end product Microsoft added to compete with mySQL.  Of course, it strips out some nice features like database snapshots.

Below is the code I found to do the same thing as database snapshot.

ALTER DATABASE demodatabasename
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE demodatabasename
FROM DISK = 'C:\DB_Snapshots\demodatabasename.bak'
WITH MOVE 'demodatabasename' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\demodatabasename.mdf',
MOVE 'demodatabasename_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\demodatabasename_log.ldf',
REPLACE
ALTER DATABASE demodatabasename SET MULTI_USER
GO

In SQL Server Management Studio, on the left side locate SQL Agent and expand the tree.  Right click on Job and select "New Job".  After you name your job, you'll want to go to "Steps" and click "New".  This window will allow you to paste the above code in for execution.  Be sure you are executing on the MASTER database.

Happy Coding...