Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Automating a Backup and Restore across Servers

    Hi,

    I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

    Thanks,

    Maria

  2. #2
    Join Date
    Dec 2004
    Location
    Europe
    Posts
    20
    Hi Maria,
    try if putting the statement

    SET XACT_ABORT OFF

    in the TSQL-stream of the step solves your problem.

Posting Permissions

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