Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11

    Unanswered: Nightly Restore Of Production db To TEST db

    Our SQL server was setup by our ERP software provider. This included a job which re-created TEST database each night at 8:00 PM, from a backup of the production, or possibly directly from the db.

    The job has been failing. I don't know why. Figure creating a new job may resolve the problem.

    Maintenance Plan includes "6:00 PM Backup To Another Server". The db backup filename's format is: ERPDB_db_200906151800.BAK

    Can someone provide (1) tutorial website or (2) instructions on how to create a new job to re-create TEST?

    If it is of any help the current job parameters include:
    Jobs > Properties > Steps:
    Type: Operating System Command
    Command: DTSRun /~Z0x3D2528587870C42675F01B889791773C41

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    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.
    Last edited by PMASchmed; 06-17-09 at 11:02.

  3. #3
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    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)
    Database: OurProductionDB

    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...
    Huh? :-)

    Again, thanks for the reply.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Sorry to confuse you with the scripting nonsense.

    but...

    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.
    Last edited by PMASchmed; 06-17-09 at 14:18.

  5. #5
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    The job was successful last night!
    Great.

    Thanks for your help and the little extra tidbits to tuck away for the future.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •