How to create automatic restore Prod DB backup to Test DB

The USER in question is forcing me to Restore the Production Data base backup from Sunday Night overtop of the

Test database early EVERY Monday Morning before they come in.

I can do this by hand

They do NOT want it done via replication or hand Export of production and hand Import of data. They want this to be a scheduled task.

I have gone into the DTS wizard and I am able to drop all the TEST data, objects etc.

And I can restore the PRODUCTION db to TEST, but only by hand.

Is there a way to have the DTS package read a backed up file on the disk and restore it to the TEST db.

I know this sounds lame, but that is their desire, and I'm new to MS-SQL...running MS-SQL 2000

if anyone know how to I would appreciate it, the DTS is NOT very intuitive IMO, and a bit confusing.

Sonoma

This can be done as a step on a job.
First, create a backup command and restore command in Query Analyser and verify it works.
Second, put in 2 steps on a job scheduled when you want.Sure, if first step is succes then next to second.

In other hand simplify the problem choosing a same name for name of database backup (Sure if the name is bult differently you can consider using global variable for parameter in a package , but be simple )|||

OK, Think I got it.

1. go into query analyser, create a step a./1 = Backing up the database in question: actually this step is already done, so doing a 'IF EXISTS' dir function might solve step 1/a.

2. in analyser, create step 2/b = Restoring the database but with a different name [?] or with a move function [?] to a different file in the same directory. This latter is a bit of choice I guess.

3. Make sure step 2 is dependent on step 1 success.

4. schedule job to run at desired time.

sound correct.

thanks for your help. BTW

rik