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

how to copy some stored procedure in SQL 2005

How to copy only some stored procedure from dev server into prod server in sql server 2005? Should I use import/export wizard or copy database wizard? Thanks in advance.

Right click on the database in the object explorer, goTasks->Generate Scripts. SetScript Object-Level Permission toTrue, click Next, check only theStored procedures, you will see a list of SPs after clicking Next. Pick the ones you like and go Next, then select preferred script mode.

|||

Thanks jack for your reply. Is there a way to differentiate between stor proc that is already in the prod server and which are not? Thanks.

|||

It's better to use 3rd party tool to handle the comparisons. Even if a SP exist in the production server, it does not mean it's has not been altered on your dev server.

Here's a good but expensive one:http://www.red-gate.com/products/SQL_Compare/index.htm

Visual Studio Team Edition for Database Professionals edition can do this too, but it's even more expensive:http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx

|||

Thanks.