How is the job running ? via the agent ? Check your DTS jobs, has that been updated because you are using an id in the DTSRun for the package (which it tied to a version) ,safer using the package name in the DTSRun. The packagename is probably in the SQL Agent job name if it was scheduled from DTS Designer. Also, add a log file either via the DTSRun or via the Agent job under "advanced" in the job settings of the agent job, it will give you a clearer idea of why the job is failing, post the log output.
.ie. DTSRun /N"PackageName" /E /L "logfilename"
I do this all the time for our staging dbs, but do it via "dumper/loader" shell scripts.
After backup runs on prod, I have a script on staging that restores the database via the backup file, updates stats and fixes the users.
You can either copy the backup file to local server, or set up sharepoint on the prod server and restore via a UNC path the the test server and schedule via Agent or windows scheduler.
LOL - Most of what you are saying is going right over my head. Not really, but, it is unfamiliar territory. Thanks for the reply. Let's see if I can answer your questions.
> How is the job running ? via the agent ?
I'm going to say the agent. I can find the job in Enterprise Manager:
Console Root > Microsoft SQL Servers > OurServer3 > Management > SQL Server Agent > Jobs > Update TEST
> Check your DTS jobs, has that been updated...
DTS? Data Transformation Services?
DTS > Local Packages > Update TEST
When I look at the properties of DTS Properties: Update TEST I see:
Title Bar: Copy SQL Server Objects Task Properties
Server: localhost (I'm assuming since I am looking at this from the SQL Server it is the valid server and I don't need to explicitly specify OurServer3
Use SQL Server Authentication
A Username and Password (username OK, password has not been changed)
Updated? The job? Not that I know of. Some component of SQL Server? Not that I know of.
> Safer using the package name in the DTSRun.
Don't have a clue how to edit whatever you are referring to, unless you mean that alphanumeric text - DTSRun /~Z0x3D2528587870C42675F01B889791773C41
NOTE: I can identify it, but, wouldn't know how to edit it as you may be indicating.
> ... add a log file
There are entries in the Application Log, it doesn't say much:
SQL Server Scheduled Job 'Update TEST' (0x24B7CAF1A3C2E940922CC96B24F26438) - Status: Failed - Invoked on: 2009-06-16 20:00:00 - Message: The job failed. The Job was invoked by Schedule 13 (Update TEST). The last step to run was step 1 (Update TEST).
And last, but not least:
> After backup runs on prod, I have a script on staging...
Not that you can do it now, but you can edit the DTS Job and right click -> execute which will run the DTS job via the DTS interface. If the job runs successfully, then it's something to do with the agent job (that references the DTS Job). Also, If you right click the agent job and view history, then click the details box, it will may tell you where/why the job failed. Since you are using a copy objects task, maybe someone dropped an object that was selected in the DTS Copy objects task.
In DTS, you can right click the package in the listing and look at "versions" to see if anyone updated and saved a new version lately.